Results 1 to 12 of 12
  1. #1
    jlolt is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    2

    Time Calculation and Summarization

    Hello,

    I'm trying to do a time calculation for a call center. I've created a database so people can enter in service tickets. There are two time related fields; 1. Ticket Opened, 2. Ticket Closed. A person can have multiple tickets opened at the same time, so the times can overlap.

    I'm trying to figure out a way to calculate the actual time a person is working for the day, week, month, etc. I cannot use the min and max function as that assumes that a person is working the full time frame which may not be the case. I was able to figure out how to do this in Excel, but an unable to translate it over to Access because of the logic refers to the date in rows and Access doesn't view the date in the same manner. For example:

    Ticket Date Time In Time Out
    123 7/16 8:00 9:00
    456 7/16 8:15 8:45
    789 7/16 9:10 9:15



    Using the min and max function the time spent would net out a total time of 75 mins (8:00 - 9:15).
    Calculating out the time spent by ticket and summing that value overstates the time spent as well. That would net out to 95 mins (8:00-9:00 = 60 mins, 8:15-8:45 = 30 mins, 9:10-9:15 = 5 mins).
    In actuality, this person would have worked 65 mins (8:00 - 9:00 = 60 mins + 9:10-9:15 = 5 mins).

    I have attached a copy of the Excel based logic that does the calculation properly for reference. If what I'm asking to do is unclear, please let me know and I will try to explain. There is also the possibility of a ticket being worked on over midnight so ideally the solution should be able to be flexible enough to span multiple days.

    Time Exercise.zip

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in access you want the full DATE/TIME for both In & out, so a query to fix:
    123, 7/16/19 8 am , 7/16/19 9 am

    then a query to calculate difference even if it goes over midnite. access uses DateAdd( ) and DateDiff() functions
    DateDiff("h", [StartTime],[EndTime])

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    how are you determining the 60 minutes used in this statement? Seem like an important part of the puzzle

    In actuality, this person would have worked 65 mins (8:00 - 9:00 = 60 mins + 9:10-9:15 = 5 mins).
    had a look at the formula in the excel spreadsheet and could really do with that being expressed in English

    you say you 'can do it in Excel', but in reality the formula has to be adjusted for each day depending on the number of rows per day

    with the exception of pivots I can't think of anything you do in excel that can't be done in access - in terms of data manipulation/calculations

  4. #4
    jlolt is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    2
    Apologies, if wasn't clear. Ticket 123 was opened at 8:00PM and closed at 9:00PM. Ticket 789 was opened at 9:10PM and closed 9:15PM. 8:00PM to 9:00PM is 60 mins and 9:10PM to 9:15PM takes 5 mins. Therefore the total time worked (ignoring ticket 456 as it fall within the same time period of ticket 123) for this person was 65 mins. Part of the issue is that I'm not entirely sure how the Excel formula works, just that it does cause I manually calculating the time interval to ensure accuracy. I didn't mean it can't be done in Access, I know there has to be way, I was just saying that I couldn't alter the formula to be in the right language in Access.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    At first, I thought maybe a subquery, then I saw the spreadsheet.
    IMHO, this is one of those things that Excel is supposed to be used for. After looking at the posted data and a formula that's so long it almost needs chapters, it's clear that sometimes the calculations will be between in and out columns BUT often between concurrent "outs" as well. Nix the subquery thought.

    We're always professing here that Excel and Access are totally different tools but it's usually directed to those who attempt to design a db like a workbook. Here's a situation where you're already using the right tool for the job. If you need a db to provide all the great UI capabilities, then fine. Just do the calculations where best, which is even more sensible if you've got something that works.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How would Excel or Access know to ignore ticket 456? Looking at your Excel and sort of follow the formula.
    I think accomplishing equivalent in Access will require complex VBA custom function. Any calculation that is dependent on data in other record(s) gets complicated in Access. Just to get an idea of what I mean, look at how query pulls data from another record, review http://allenbrowne.com/subquery-01.html#AnotherRecord

    Agree with Micron, Excel probably best tool. I once had requirement for matrix multiplication. I had a spreadsheet that did this and wanted to eliminate Excel component from database project. I found equivalent VBA. I compared 3 methods: 1) Excel spreadsheet; 2) Access VBA calling Excel functions; 3) Access VBA without Excel functions. Each produced different output.
    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.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I considered 2 for a sec but figured that was pointless if I believed what I wrote. I'm surprised that you got different results between 1 an 2. Was rounding at play?
    Last edited by Micron; 07-16-2019 at 03:55 PM. Reason: dang phone!

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Ticket 123 was opened at 8:00PM and closed at 9:00PM. Ticket 789 was opened at 9:10PM and closed 9:15PM. 8:00PM to 9:00PM is 60 mins and 9:10PM to 9:15PM takes 5 mins
    so to put this into English

    'regardless of the number of tickets in a day, take the ones with the longest continuous time as being the 'solid' time. Then add additional ticket times that start and end outside of that tickets start and end time'

    to clarify whether this is correct - from your example in your initial post, if ticket 456 ended, not at 8:45 but instead at 9:15 this would be ignored? or would you add 15 minutes for this ticket?
    And if you did, would you then ignore the 5 minutes on ticket 789

    or to put it graphically

    original
    ticket......8:00....8:15.....8:30....8:45....9:00. .9:10....9:15
    123........|-----------------------------------|
    456...................|-------------------|
    789............................................... ................|-------|
    worked...|-----------------------------------|.........|-------|

    or per my alternative
    ticket......8:00....8:15.....8:30....8:45....9:00. .9:10....9:15
    123........|-----------------------------------|
    456...................|------------------------------------------|
    789............................................... ...............|-------|
    worked...|---------------------------------------------------|

    and another
    ticket......8:00....8:15.....8:30....8:45....9:00. .9:10....9:15
    123........|------------------|
    456...................|-------------------|
    789............................................... ................|-------|
    worked...|----------------------------|..................|-------|

    doesn't all quite line up, but you should get the idea - dashed area is the worked time

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Quote Originally Posted by Micron View Post
    I considered 2 for a sec but figured that was pointless if I believed what I wrote. I'm surprised that you got different results between 1 an 2. Was rounding at play?
    Rounding was my conclusion but can't really say for sure. Option 3 had greatest variance.
    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.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    assuming my interpretation in my previous post is correct, this query works and produces the same result as the spreadsheet

    1. create a table with one column and populate with the number 1-1440 (the number of minutes in a day) -or use my flexible counter query at the end of this post

    2. then use this query
    Code:
    SELECT C.aDate, Count(C.worked) AS CountOfworked
    FROM (SELECT DISTINCT DateValue([F1]) AS aDate, 1 AS worked, admCount.Counter
    FROM Table1 AS R, admCount
    WHERE (((admCount.Counter) Between DateDiff("n", DateValue([F1]),[f1]) And DateDiff("n", DateValue([F1]),[f2])-1)))  AS C
    GROUP BY C.aDate;
    admCount is the table/query of numbers, Table1 is just the TimeIn, Timeout datestamp columns from the excel spreadsheet

    The DISTINCT query simply records whether one or more tickets were being worked on that particular minute and if so populates with 1 - which is then summed in the outer query by date

    aDate CountOfworked
    11/03/2019 209
    12/03/2019 169

    with regards over midnight - that works but the time will be put to the day in which the minute occured - so a ticket running from 11:30pm-12:15am will show 30 mins in the first day and 15 in the second.

    Note I included a -1 in the criteria - this was to match the spreadsheet which treats 4:30pm-4:45pm as 15 minutes - depends on the time recording but it can be argued that it is 16 minutes.

    the flexible counter query
    1. create a table with one field and populate with 0-9 (table called admcounter, field called ctr)

    2. then create this query and save as admCount
    Code:
    SELECT CLng([singles].[ctr]+([tens].[ctr]*10)+[hundreds].[ctr]*100)+([thousands].[ctr]*1000) AS [Counter]
    FROM admCounter AS tens, admCounter AS singles, admCounter AS hundreds, admCounter AS thousands
    ORDER BY CLng([singles].[ctr]+([tens].[ctr]*10)+[hundreds].[ctr]*100)+([thousands].[ctr]*1000);

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, eating my hat. Amazing solution, Ajax
    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.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    thanks

    just demonstrates that excel and access don't solve the problem the same way

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

Similar Threads

  1. Time Calculation
    By DMT Dave in forum Access
    Replies: 1
    Last Post: 05-05-2019, 09:14 AM
  2. Replies: 1
    Last Post: 04-09-2018, 12:35 PM
  3. Time calculation
    By anlimah in forum Programming
    Replies: 1
    Last Post: 07-07-2014, 04:48 PM
  4. time difference calculation help
    By JayRab in forum Access
    Replies: 5
    Last Post: 02-03-2014, 01:32 PM
  5. Time interval calculation
    By gar in forum Queries
    Replies: 3
    Last Post: 04-09-2013, 02:05 PM

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