 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2008Calculation in Query
I was told that it is easier to do a calculation in a query but I can't get it to work. I want to add up a bunch of values in filds to give me the total. This is what I have entered right now but it does not give me a total it just remains blank. Field: total: [Layout]+[Program]+[sawing]+[Morbi]+[Edgeband]+[Planer]+[Veneer]+[Press]+[Sanding]+[Assemble]+[Laminate]+[Other]+[Finish]+[Ship/Rec]+[Office] Total: Sum Can anyone tell me what i need to change in order to make this work. Not sure if this will work but put an Nz in front of all the boxes... so it would look like Nz([Layout])+Nz([Program])+etc > I was told that it is easier to do a calculation in a query but I can't get > it to work. [quoted text clipped - 7 lines] > Total: Sum > Can anyone tell me what i need to change in order to make this work. If anyone of the fields is blank (Null) you will get nill - blank. Use this -- Nz([Layout],0)+Nz([Program],0)+Nz([sawing],0)+Nz([Morbi],0)+Nz([Edgeband......  SignatureKARL DEWEY Build a little - Test a little > I was told that it is easier to do a calculation in a query but I can't get > it to work. [quoted text clipped - 7 lines] > Total: Sum > Can anyone tell me what i need to change in order to make this work. I could be wrong but I'm getting that twitchy feeling about the design of this database. Those fields sound suspiciously like something that ought to be in a list in a table called ProcessesUsed ProcUID JobNumber ProcessID ProcPrice (price charged on this occasion) (and other stuff about that particular job ) which is fed from a table which lists all the possible processes. ProcessID Process (lists such items as Planer, Veneer, Press DefaultPrice Evi > I was told that it is easier to do a calculation in a query but I can't get > it to work. [quoted text clipped - 4 lines] > > Field: total: [Layout]+[Program]+[sawing]+[Morbi]+[Edgeband]+[Planer]+[Veneer]+[Press]+[Sa nding]+[Assemble]+[Laminate]+[Other]+[Finish]+[Ship/Rec]+[Office] > Total: Sum > Can anyone tell me what i need to change in order to make this work. Well, what this table is, is a timesheet. On the main form is the job number then on the sub form I have the info: Date, Employee and then the categories that I stated below, when the employees fill these out thy put the hours for that job under the catergories that they completed work on. They can do multiple jobs per day on each job. Not sure if that clears that up a bit. > I could be wrong but I'm getting that twitchy feeling about the design of > this database. [quoted text clipped - 27 lines] > > Total: Sum > > Can anyone tell me what i need to change in order to make this work. My twitch was right. You are suffering from Spreadsheet Withdrawal :) The structure you ought to have is TblEmployee EmpID FName SurName etc TblProcess ProcID ProcNo (in case you need to do the processes in a particular order) Process (eg Planer, Veneer, Press) TblJob JobID JobNumber Other fields about the job eg who commissioned it - depending on your needs TblJobCompletion JCID JobID (linked from TblJob JobDate EmpID (linked from TblEmployee) ProcID (linked from TblProcess) HrsUsed (how long that employee took to complete that process for that job) You input your data not into a table but with a form which has combo boxes to let you select Employee and Job If you wish you can even fill in the processes and leave those people who complete them to choose their name next to the process they completed and fill in the hours they worked. With this structure it is easy to total the hours used in your reports or queries, total the hours that each employee spent in a week, total the hours spent oon Planing on all your jobs It is also very easy if you find that you have to add another process. Evi > Well, what this table is, is a timesheet. On the main form is the job number > then on the sub form I have the info: Date, Employee and then the categories [quoted text clipped - 29 lines] > > > > > > Field: total: [Layout]+[Program]+[sawing]+[Morbi]+[Edgeband]+[Planer]+[Veneer]+[Press]+[Sa > > nding]+[Assemble]+[Laminate]+[Other]+[Finish]+[Ship/Rec]+[Office] > > > Total: Sum > > > Can anyone tell me what i need to change in order to make this work. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.