Results 1 to 10 of 10
  1. #1
    blimbert is offline 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. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,125
    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.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,517
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    blimbert is offline 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. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,125
    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. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,803
    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!! )
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  7. #7
    blimbert is offline 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.



    Attached Files Attached Files

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,803
    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??
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  9. #9
    blimbert is offline 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. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,803
    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)
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sum of worked hours
    By infratunes in forum Access
    Replies: 9
    Last Post: 10-24-2018, 12:29 AM
  2. Replies: 2
    Last Post: 11-09-2015, 01:24 PM
  3. Replies: 4
    Last Post: 01-17-2014, 10:53 PM
  4. calculate rate * hours worked
    By hamish mather in forum Queries
    Replies: 1
    Last Post: 12-22-2011, 06:14 AM
  5. Sum hours worked, grouped by dep't
    By goodfood in forum Queries
    Replies: 3
    Last Post: 05-05-2011, 06:11 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums