Hi All

Hope I can get some help building a query to pull the "Work hours per shift" out of a total time.
I'll give you a little background on this database and hope this will help. We are trying to track the Special Details hours that our Security Guard force does and we need to be able to break out the time by shift so we can see which shift has the most load of Special Details.
Example Scenario
Start DT is 2/7/2010 6:00am and the End DT is 2/7/2010 6:00pm, Total Time = 12 hours
A Special Detail is started at 6am, a security guard is placed on the Detail and he works till his shift is over at 8am (2 hours). He is replace by another security guard who works his entire shift, 8 am to 4pm, (8 hours). Then he is replaced by another security guard who works from 4pm to 6pm, (2 hours) and then the Detail is closed.
Total Special Detail hours is 12 but it was worked across 3 different shifts.
First Security Guard on the 12 to 8 shift worked 2 hours
Second Security Guard on the 8 to 4 shift worked 8 Hours
Third Security Guard on the 4 to 12 shift worked 2 hours
I am using the following statement in a query to get my total hours
Expr1: DateDiff('n',[Start DT],[End DT])/60
But...How can I break this down to get the following results??
12 to 8 shift (2 hours worked)
8 to 4 shift (8 Hours worked)
4 to 12 shift (2 hours worked)


By the way, the shift hours are
12am to 7:59am
8am to 3:59pm
4pm to 11:59PM

Thanks in advance
Ken

You don't say how you are recording the start and end times for the worker. But a suggestion is to add a further field to the form which enables the user to indicate the total number of Special Duty hours they worked if any during their full shift. Then use this as the basis of your calculations.

Attached is the form used to enter the Special Detail info

Ok, so to the right of the Total Hours field add another text box that has the label special duty hours. Then get the user to record how much of the total hours needs recording as special duty hours.

Then you can use this to subtract from the total time to give you a breakdown of normal and special duty hours. I know it's another field but it will simplify the whole system with the minimum amount coding etc.

David
Thanks for the quick reply.
I had thought about doing it that way but I was hoping to try and simplify the entry form for the guards.

Thanks again
Ken