Need the SUM TOTAL in Hours for Time Out-Time In

[trevorC via AccessMonster.com]

Hi Can you help,

Short description -
Output required is Hours/minutes worked each day for selected period taken
from start and end times
Total minutes worked for selected period
Sum of Total minutes worked for selected period - shown in Hours
EG
Day Time Total mins Total Hours
Mon 6.00 - 16.00 600 10
Tue 6.00 - 16.00 600 10
Wed 5.00 - 16.30 660 10.5

Long description -
I have a strange problem with this query, the first query shows what i expect
- Hours+Minutes, Hours, Minutes and Total Minutes. My second query should
then Sum the Total Minutes to show all time worked for the selected period.
This is then converted into Hours and minutes and when this happens the sum
is not correct. I think that it is the way it divides the total minutes by 60
to get the hours but the remainder is incorrect. Is there an easy way to do
this.


SELECT Employees.[First Name], Employees.[Last Name], Format([scan time in],
"h:nn AMPM") AS ['Scan Time In], Format([scan time Out],"h:mm AMPM") AS
['Scan Time Out], Format([Scan Time Out]-[Scan Time in],"h:nn") AS [Total
Hours], Format([Scan Time Out]-[Scan Time in],"h") AS Hours, Format([Scan
Time Out]-[Scan Time in],"nn") AS minutes, (([hours]*60)+[minutes]) AS [Total
Minutes]
FROM Employees RIGHT JOIN [Time Tracking] ON Employees.Barcode = [Time
Tracking].[Employee ID]
WHERE ((([Time Tracking].[Scan Date Out]) Is Not Null))
ORDER BY Employees.[Last Name], [Time Tracking].[Scan Date Out];

SELECT [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
[Last Name], Sum([Total Minutes]/60) AS [SumOfTotal Hours]
FROM [Time Keeping Base Report]
GROUP BY [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
[Last Name];

 

[Duane Hookom]

Why all the formatting in the query? Keep it all date/time or numeric until
you get to a report or form control where it is appropriate to format. Your
formatting just converts workable data values to useless strings.

 

[John Spencer]

Sum([Total Minutes])/60

That should give you hours and fractions of an hour


To get hours and minutes use expressions like:

Sum([Total Minutes])\60 as WholeHours
Sum([Total Minutes]) Mod 60 as RemainingMinutes


SELECT [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
[Last Name], Sum([Total Minutes])/60 AS [SumOfTotal Hours]
FROM [Time Keeping Base Report]
GROUP BY [Time Keeping Bas

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County