How to count continuous hours in form / query

[david1028]

Hello, i have developed a user form for employees to enter their hours worked for each department for each day they work. They may have over 5 different work codes for each day.. work 8 - 10 for FM, then 10 to 12 for GGB and 1 to 4 for DPW. I need to track total hours for the day, which i know how to do, but how can i track consecutive hours worked for different departments?

I need a text box to count continued hours worked the above example would be 8 - 12 would be 4 continuous hours.

Thank you for any help or suggestions.

 

[Mark_]

How are you saving the data?
One record for each period?
One record for each day?

Need more details before we can help you. Please at least give us an idea how you are storing these period.

 

[david1028]

one record for each period... i sort the records by weekending date, then by workdate and time worked.

Thank you for any help

 

[Mark_]

So have you created a query yet that gives you the amount of time worked for a given record? Something like

TimeWorked: Dt_End - Dt_Start

Or does each record have start time/duration or some similar way of tracking? Also, is it saved in a text field, number, or as a date?

 

[david1028]

yes i have a query the calculates the time worked - ShiftHours: DateDiff("n",[starttime],[endtime])/60 and is saved in a text field
Thank you again

 

[Mark_]

For myself, I wouldn't divide your result by 60. I would keep it as minutes.

As you have a query the generates your base data, I'd make a second query to do the sum. SQL for your new query should look like this:

SELECT DISTINCTROW YourQuery, Sum(YourQuery.ShiftHours) AS [Sum Of ShiftHours]
FROM YourQuery
GROUP BY YourQuery.Project;

This should total by hour for each project. Just make sure that your original query limits based on what ever criteria you need. You'll probably need that query for other things so this will simply be using an existing piece you already have.

 

[david1028]

thank you but the issue was not getting the sum. I need to the total of continuous hours worked.

Day of Week Dept Hrs Worked Total Hrs Continuous
MOnday GGB 12 - 2 2

 

[david1028]

Sorry i sent before completion
Monday GGB worked 8 to 10 for ggb total worked is 2 hrs
Monday FM worked 10 to 12 for wm total worked is 2 hrs, but 4 continuous
I need to know how to get the continuous hours
Thanks again

 

[Mark_]

Are you looking for a running total then?

If so, see if this will help.

 

[david1028]

That may help, I will review how the running total works.
I only need a running total if the timeend and timebegin for the new department are the same. Maybe i can use Runtotal in and IIF statments...