Consolidating Hours Worked in Day Query

1. Novice
Windows 10 Access 2016
Join Date
Sep 2018
Posts
10

Consolidating Hours Worked in Day Query

Hello, I cannot figure out how to return the desired results of a recordset that I have. I am trying to determine two things:
1). The unique or distinct total amount of hours worked in a day in total from various people (not summing the total hours, only the hours that do not overlap from various workers)
2). If the any of the workers' time worked spanned overnight (counting 11:59PM as the day of record)

 Worker StartDate StopDate A 1/1/20 7:00AM 1/1/20 4:00PM B 1/1/20 2:00PM 1/1/20 10:00PM C 1/1/20 11:00PM 1/2/20 6:00AM A 1/2/20 3:30 PM 1/3/20 7:00AM B 1/3/20 7:00AM 1/3/20 12:00PM C 1/4/20 8:00AM 1/4/20 5:00PM

I'm trying to write a query(s) that total the hours during the day, as well as have a field as to whether anyone had hours that spanned 11:59PM.

So, I would expect to see:
 Date Hours Overnight Explanation 1/1/20 16 Y 16 hours = 9 from A, 6 from 4:00PM to 10:00PM from A to B, 1 from 11:00 to midnight for C) 1/2/20 14.5 Y 14.5 hours = 6 from C from 12:00AM to 6:00AM, 8.5 from A from 3:30PM to 12:00AM 1/3/20 12 N 12 hours = 7 from A from midnight to 7:00aM, 5 from B from 7:00AM to 12:00PM 1/4/20 9 N 9 hours from C

I am unable to get my head around how to structure and summarize the data.

Any help would be greatly appreciated! Thank you kindly

2. Virtually Inert Person
Windows 10 Access 2016
Join Date
Jun 2014
Location
Posts
7,045
My bias is code, so if this were my problem I'd probably Google some searches and in the absence of finding anything, would likely end up looping through my recordset in code. Someone may chime in with a query solution (probably would be pretty spectacular given the logic involved) but that surely won't be me. So, a code approach might be like
- starting at 1st record, compare int(start) to int(stop). If they are the same, it is the same day so get the elapsed time via DateDiff
- if not the same AND int(end) = int(start) + 1 [if not, there is a problem] then
- - get DateDiff between start and int(start) + #11:59:59# and add Datediff between int(start) + #11:59:59# and end time. Write that calculation somewhere.
- - At the same time, you could flag the Y/N field as Y
- move next record
- rinse and repeat

Sometimes I make things more difficult than they need to be; not sure if this is one of them but I hope you can see the logic behind it.

3. You want to include hours that are the difference between one record end time and another record begin time? Look at this tutorial about getting value from previous record.
http://allenbrowne.com/subquery-01.html#AnotherRecord

Then you want to break a record at midnight.

Agree with Micron. I rather doubt this can be done by query alone and likely requires complex VBA procedure.

4. Novice
Windows 10 Access 2016
Join Date
Sep 2018
Posts
10
Thank you so very much for the fast response! The hard part for me is I have a good understanding of the queries but not VBA. So I can vaguely see the logo you describe, but don't know how to even begin lol.

5. Virtually Inert Person
Windows 10 Access 2016
Join Date
Jun 2014
Location
Posts
7,045
I'd say give this a day or so. I have been amazed at what some people around here can do with queries, so a query solution might get presented. If not, June7 might be chomping at the bit to take this one on

EDIT - in fact, it's a bit more complicated than what I posted as you also have to verify who the worker is. Not sure I'm seeing a need for comparing the next record to anything though. I get the impression that the start and end times for any worker are in one record unless I've missed something. In that case, I'd be writing the values to a table where they can be DSum'd or a Totals query could be run off of.

6. Master of Nothing
Windows 7 32bit Access 2010 32bit
Join Date
Sep 2010
Location
Posts
8,713
I would also use code. I created a table and have been trying to get any type of result that is close but there are a couple of hitches.

It looks like this is part of a time sheet. Since you want to total the hours worked in a single day using a query, the hours should be broken by date so each day is a record.
Looking at only the date 1/1/2020, instead of
 Worker StartDate StopDate A 1/1/20 7:00AM 1/1/20 4:00PM B 1/1/20 2:00PM 1/1/20 10:00PM C 1/1/20 11:00PM 1/2/20 6:00AM <--spans 2 days

the records in the table should be
 Worker StartDate StopDate A 1/1/20 7:00AM 1/1/20 4:00PM B 1/1/20 2:00PM 1/1/20 10:00PM C 1/1/20 11:00PM 1/1/2020 11:59PM <--shift started C 1/2/20 12:00AM 1/2/20 6:00AM <-- shift ended
Then a query could be used to get the totals.

The second thing is that there are 3 records for 1/1/2020 and only 1 workers hours spans days. But you are not indicating that only 1 worker hours spans 2 days.
Code:
```Date     Hours     Overnight     Explanation
1/1/20    16          Y          16 hours =
9 hours from A,
6 hours from 4:00PM to 10:00PM from A to B,
1 hour  from 11:00 to midnight for C)   <---- spans days```

You've said HOW/WHAT you want to see the hour totals, but what are you doing with them? Do you want to view them in a report? On a form? Should the results be written to a table?
It is easy to write a UDF, but without some context, it is difficult to give a focused response.

Edit: I threw together some code....... (I've got to get a life!! )

7. Novice
Windows 10 Access 2016
Join Date
Sep 2018
Posts
10
ssanfu, I am at a loss for words! I am so very grateful and appreciative you took the time to work on this to the extent you did. I'm unbelievably impressed with how fast you built that, wow! To answer your last question I plan on using the data to bill a customer, who needs the total hours we worked on a project in a day, but cannot include any overlapping hours. There is also an extra charge if we were there overnight. So, I think all I need is to be able to query the temp table you built, and I can grab the sql from the subform (THANK YOU!)

I do have two issues, however.

Issue 1 - Sum only "unique" hours worked
On 1/1/20, there should be 16 unique/non-duplicate hours worked in the day (9 from A for 7:00AM to 4:00PM, 6 from 4:00PM to 10:00PM from B, 1 from 11:00 to midnight for C - note that the two hours over overlap from 2:00-4:00 overlapped with A & B I can only count once). I've looked at the code but my VBA is pretty limited, I do not know how I could only sum the non-overlapping times?

Issue 2 - Count multiple Days as 24 hours
If I enter a record starting 1/6/20 8:00AM, ending 1/9/20 8:00AM, the db returns 1/6/20 16 hours w overnight (correctly), and 1/9/20 8 hours non overnight (also correct). However, it is not creating 1/7/20 24 hours overnight and 1/8/20 24 hours overnight. I'm sorry I should have included this possibility in the original example data. Feels like I need to tweak the code to increment from sDate until it equals eDate and append the 24 hours w overnight for each date in between, but I'm not sure how/where to modify that.

Thoughts on how to tweak for both of these?

Thank you again so very much, this was so helpful! Sincerely, Brian

Edit - I believe I have corrected Issue 2 by adding a few lines of code, and commenting out a few others. I am still stumped on Issue 1. This revision is attached.

8. Master of Nothing
Windows 7 32bit Access 2010 32bit
Join Date
Sep 2010
Location
Posts
8,713
Try this one.

I see that worker D start date/time is 1/6/2020 8:00:00 AM and end date/time is 1/9/2020 8:00:00 AM.
Could there also be worker B start date/time is 1/8/2020 8:00:00 AM and end date/time is 1/8/2020 8:00:00 PM.
Would the hours for 1/8/2020 be 24 or 36??

You are doing extensive testing, right??

9. Novice
Windows 10 Access 2016
Join Date
Sep 2018
Posts
10
Thank you ssanfu for the continued response, it is much appreciated!

Great catch on the other workers occurring in the range. Because I only want the unique hours worked I the day, the results should be 24 hours.

I tried two other examples in your MOD2 with different results
1). Added Worker B start date/time is 1/8/2020 8:00:00 AM and end date/time is 1/8/2020 8:00:00 PM. The result on that decrements the total hours to 12 on 1/8/20 (as opposed to the 36 that I thought may have results).
2). I added Worker C on start date/time at 1/9/2020 12:00 AM, and end date 1/9/20 8:00AM. Given how the previous instance resulted, I was expecting a decrement of 8 hours on 1/9/20. However, it added 8 hours to yield 16 for 1/9/20.

Thoughts on what I may need to tweak?
Brian

10. Master of Nothing
Windows 7 32bit Access 2010 32bit
Join Date
Sep 2010
Location
Posts
8,713
Apologies about missing "unique or distinct total amount of hours worked in a day" (in your first post).

I do have a question: How many records are involved? Will there be 100's (for a month)? Or how many per time period?

After thinking about the requirements to get the proper results, there are a couple of ways to go.
1) Use a custom collection. I've just begun to learn how to create custom collections.
2) Use an Array. Easier but you have to sort the array before calculating the hours.
3) Use a 2nd temp table. Probably the easiest (at least for me right now. - I'm short on time)

So I see the main problem is that the end date/time can span multiple days. There is a record that starts on 1/6/2020 8:00 AM and ends on 1/9/2020 8:00:00 AM.
It would be better to have a record for each date (the 6th, 7th, 8th and 9th)...... that would be the "Normalized" structure. But since that is not real life, time to code.

Looking at the spreadsheet, (helped me visualize the data)
Step 0 - is the original TABLE data of dates/times.
Step 1 - is a query opened on the table, sorted by startdate.
We can now determine which records spans multiple dates. Code will then add/adjust records for the missing date/times.

Step 2 - the new to table to hold the added/adjusted start/end date/times.
Step 3 - a new query (in code) used as the source to calculate the hours and determine which hours (if any) should be written to the 2nd temp table.
Step 4 - use a totals query on the 2nd temp table for the report. (existing)