Query to total hours worked per day

[cds5819]

Hi all, I'm hoping someone can help me out with this, or at least point me to a specific post where this has already been covered. I'm sure this is probably very simple, and I'm just overthinking it.

I've been tasked with building a payroll database in Access 2007 for our per diem staff. They can work on multiple cases in one day, and some of those cases do carry over into the next day. Also, multiple staff members can work on the same case, so there would be multiple entries for the same case, which links to the main Case table in a (many-to-one relationship). The table for their hours is called Time Card Hours, and is set up as follows:
CaseID - text
StaffCode - text
DateIn - short date
TimeIn - short time
DateOut - short date
TimeOut- short time

Example: Tech1 starts a case at 8:30 PM on 10-20-2012 and finishes at 3:00 AM on 10-21-2012. Tech1 then starts another case at 10:45 AM and finishes at 4:00 PM. Tech1 then logs time from 6:00 PM to 7:30 PM on paperwork, before getting one last case, starting at 11:10 PM, and ending at 4:35 AM on 10-22-2012.

I've been able to successfully calculate the total time spent on each case, even spanning midnight, with DateDiff, but I haven't been able to figure out how to calculate the total time spent working each day by the tech. :banghead: This is extremely important because California law dictates that overtime be applied if more than 8 hours are worked in any given day. I'm really in a bind here, can anyone help me out please?

Also, and this is more of a general question, but are date and time values easier to calculate when they are stored as a date/time data type or as a numerical value? And should date and time be combined in one field, or should they be separated as I currently have the fields now? Please let me know if there is any more information that I can supply. Thanks in advance.

 

[MeilingM]

I'm new to Access and Databases, but here's my stab at it...

Given your Time Card Hours table:

Hours for all entries for Tech1 that start and end on 10/21/2012
+ (Hours for all entries for Tech1 that end on 10/21/2012 - Any hours before midnight that morning)
+ (Hours for all entries for Tech1 that start on 10/21/2012 - Any hours after midnight that night)
= Tech1's hours for 10/21/2012

How to best do this in Access... <shrug>

 

[ypma]

I downloaded from MS an example using the time elapse Function, and modified it for my own use. I will attach the original and think it would give you another way, other than dateDiff. It appears to answer all your question. Let me know how you get on.

 

[Isskint]

Based on the table, i do not think you can acheive this. Without knowing a finish time for the day what time do you use? For example, if Tech1 starts a job at 21:00 but does not finish that job until 14:00 on their next shift, do you want 3 hours to midnight or 1 hour to shift finish at 22:00 (2 hours to shift finish at 23:00 etc)? Equally you need a start time for the day to calculate the time upto completion of the job, do you want 14 hours from midnight or 5 hours from day start at 09:00?:banghead:
If you can answer this issue then the basic query process is, i think, fairly easy.

Subtotal Query on StaffCode (Group By), DateIn (Group By) criteria Current Date or Yesterday's Date or ??? and then a calculated field to calculate the daily hours (SUM). This should then group each Tech and give you the sum of the hours.

On the second point, i think in this case a combined date/time field may be better.

 

[cds5819]

Based on the table, i do not think you can acheive this. Without knowing a finish time for the day what time do you use? For example, if Tech1 starts a job at 21:00 but does not finish that job until 14:00 on their next shift, do you want 3 hours to midnight or 1 hour to shift finish at 22:00 (2 hours to shift finish at 23:00 etc)? Equally you need a start time for the day to calculate the time upto completion of the job, do you want 14 hours from midnight or 5 hours from day start at 09:00?:banghead:
If you can answer this issue then the basic query process is, i think, fairly easy.

Subtotal Query on StaffCode (Group By), DateIn (Group By) criteria Current Date or Yesterday's Date or ??? and then a calculated field to calculate the daily hours (SUM). This should then group each Tech and give you the sum of the hours.

On the second point, i think in this case a combined date/time field may be better.

For the end of the day, I'm using 23:59, then subtracting the start time to find the specific work time for that day when the case carries over midnight, it was the only was I could think of, which gives me the 3:30 worked for 10-20 in my example. For the part after midnight, I hadn't worked out yet, but I probably would want to start at 00:00 and count from there, if that makes sense. Fortunately, the database has only test data in it, so if changes need to be made, I can do that easily enough now.

@ypma, thanks for the file. I'll take a look at it when I get to the office in the morning.