sum values

[juster21]

I am trying to create a query that will combine the values of 7 columns (time entered for each day of week) so that the result is the sum of all time.

For example:
Production_Hours1 = 7
Production_Hours2 = 7
Production_Hours3 = 7
Production_Hours4 = 7
Production_Hours5 = 7
Production_Hours6 = 0
Production_Hours7 = 0

Total Production Hours= 35

This is what I currently have:

SELECT Lib_Employee.EmployeeID, Lib_Employee.LastName, Lib_Employee.FirstName, tbl_ProductionHours.Prod_Date, tbl_ProductionHours.Production_Hours_1, tbl_ProductionHours.Production_Hours_2, tbl_ProductionHours.Production_Hours_3, tbl_ProductionHours.Production_Hours_4, tbl_ProductionHours.Production_Hours_5, tbl_ProductionHours.Production_Hours_6, tbl_ProductionHours.Production_Hours_7, tbl_ProductionHours.Comments
FROM Lib_Employee INNER JOIN tbl_ProductionHours ON Lib_Employee.EmployeeID = tbl_ProductionHours.Employee_ID
WHERE (((Lib_Employee.ReportsTo)=[Reports to: ]) AND ((tbl_ProductionHours.Prod_Date)=[Week Ending:]))
ORDER BY Lib_Employee.LastName;

 

[Andrew Fergus]

Hi

Add a new field to your query using this expression in the 'field' property:

Total : [Production_Hours_1] + [Production_Hours_2] + [Production_Hours_3] + [Production_Hours_4] + [Production_Hours_5] + [Production_Hours_6] + [Production_Hours_7]

HTH, Andrew

 

[juster21]

thanks so much!!

 

[VicRauch]

Juster,

This problem would be solved much better by normalizing your data rather than "fighting" with these types of problems. For an excellent discussion of Normalizing, please follow the link in my signature area titled Access Design Tips. The first link on that web page is the excellent discussion of Normalizing your data. If you have any questions about Normalizing, just ask back here and your questions will be answered.

Good luck!

 

[Andrew Fergus]

You're welcome juster21!
____________________

Hi Vic

I had thought about mentioning normalisation when I first read this post but when I recreated the query and underlying tables, I thought the set up was okay.

Juster21 separated the employee details from the timekeeping which is what we would expect as a minimum. The timekeeping was then captured on a weekly basis. So instead of entering an employee id, date and hours for every day (15-21 items per employee per week) as you would in a fully normalised database, the user only has to input the employee id, the week date and 5-7 lots of hours (7-9 items per employee per week). I actually think this is ok because it makes data entry easier and as you saw with my answer, calculating a total for a week is not difficult. I think it also provides a better data format for laying out forms and reports.

Was there anything in particular you had an issue with regarding normalisation - that I am not seeing?

Regards
Andrew

 

[VicRauch]

Andrew,

Thanks for your response. I did not bother to recreate the query with it's underlying tables, so I did not see the entire structure, nor did I see anywhere that this was a data entry app, rather than each person keying their own In/Out times. As a data entry app, I would not make a data entry person key all that data either.

My whole press on Normalization is that it makes the database SOOOOO much easier to deal with. And you are very correct that this little piece of code is not difficult at all.

I agree with you 100%. I wondered why you had not mentioned normalization, because I think you normally do as much as I do.

 

[Andrew Fergus]

Hi Vic

Agreed 100%. Normally I would mention it but not this time for the reasons above. Maybe we should have a sticky about normalisation....

Cheers
Andrew

 

[VicRauch]

I think, for as important as Normalization is, a sticky would be a great idea. Please look at the one pointed to in my Access Design Tips. It will be the top hyperlink on that page. It's not mine, I'm only pointing to it because I think it is such a good writeup.

Thanks Andrew!

 

[giacomo]

Yes, I agree that we need a sticky, we've needed one for years... I think it's been brought up before but I could be wrong. BTW, something1, something2, something3 is almost always a normalization issue (1NF no repeating sets).

Also, be careful when summing across fields as a null value in any field will evaluate to a null result. So in other words, this:

Total : [Production_Hours_1] + [Production_Hours_2] + [Production_Hours_3] + [Production_Hours_4] + [Production_Hours_5] + [Production_Hours_6] + [Production_Hours_7]

should be this:

Total : nz([Production_Hours_1],0) + nz([Production_Hours_2],0) + nz([Production_Hours_3],0) + nz([Production_Hours_4],0) + nz([Production_Hours_5],0) + nz([Production_Hours_6],0) + nz([Production_Hours_7],0)