Originally Posted by
Shred Dude
I was building a query with the intention of returning from this table the total time worked for a given employee between two dates. Using the Access Query Designer, I got to this point with the SQL looking at just the first two Time In/Out pairs...it would get much uglier for all 6 pair.
[VBA]PARAMETERS lEmpID Long, dtFrom DateTime, dtTo DateTime;
SELECT T.EmpID, T.dtWork, T.TimeIn1, T.TimeOut1, T.TimeIn2, T.TimeOut2, (DateDiff("n",[TimeIn1],[TimeOut1]))+IIf(Not (IsNull([TimeIn2]) Or IsNull([TimeOut2])),IIf(nz([TimeOut2])>0,DateDiff("n",nz([TimeIn2]),nz([TimeOut2])),DateDiff("n",nz([TimeIn2]),DateAdd("d",1,nz([TimeOut2])))),0) AS TotalMinutes, Format(TimeSerial([TotalMinutes]\60,[TotalMinutes] Mod 60,0),"Short Time") AS TotalHours
FROM tblDailyTimeSheets AS T
WHERE (((T.EmpID)=[lempid]) AND ((T.dtWork) Between [dtfrom] And [dtto]) AND (Not (T.TimeIn1) Is Null))
ORDER BY T.dtWork;
[/VBA]
It works, returning the cumulative time worked for each day , but I'm guessing there must be a better more efficient way to do not just that, but to then Group it for the aggregate sum?
It would be easy enough for me to add a column to the table to store the "Total Hours" when a record is added to the table, which would make this much simpler to do with an Aggregate query I'm guessing. I didn't get there without such a column so far.
Any perspectives on how others would approach this query and/or table layout would be most welcome.