Time Calculation and Summarization

[jlolt]

Hello,

I've been using Access for quite some time but, do not consider myself an expert. I am completely stuck on something and need some help. I have a database where users enter in tickets. Part of their entry process includes entry date, time in and time out (meaning opening the ticket and closing the ticket).

Now I'm trying to report out total time spent for a user over the course of a day, week or month. My initial thought was this is easy, just use a min and max function and take the difference. That result is incorrect because it is possible that it will have gaps in time where no ticket was worked on. I can't seem to figure out how to calculate total time when times overlap and when there are gaps in time. Min and Mix seems to overstate the total times.

I was able to find a way to do this in Excel but that logic doesn't translate into Access because it refers to data in rows and Access doesn't view data in the same manner.

Any ideas? Am I making my question clear?

 

[jlolt]

See Data Below:
User Ticket Entry Date Time In Time Out
A 1 3/11/2019 8:07:00 PM 9:00:00 PM
A 2 3/11/2019 6:21:00 PM 8:39:00 PM
A 3 3/11/2019 5:54:00 PM 7:58:00 PM
A 4 3/11/2019 5:51:00 PM 7:13:00 PM
A 5 3/11/2019 5:30:00 PM 5:50:00 PM
A 6 3/12/2019 8:31:00 PM 9:29:00 PM
A 7 3/12/2019 6:38:00 PM 7:53:00 PM
A 8 3/12/2019 5:34:00 PM 6:09:00 PM

Ultimately, I would like to be able report out that for 3/11 User A worked 209 minutes or 3 hours and 29 minutes on 3/11 and 169 minutes or 2 hours and 49 minutes for 3/12. I would also like to be able to report out that for both days the user logged 378 mins or 6 hours and 18 mins.

 

[Deleted member 147267]

If I get the drift, I'm not seeing a need for subquery and I'm assuming the overlapping time is because someone might be working on multiple "tickets" concurrently. In that case, I think you'd use a totals query and return all ticket records between start date/time and end date/time and group on person id. In short, you can't go by min/max; you have to return each start value, end value, calculated duration and sum the durations. You might have to do it in more than one step; i.e. return a sorted and grouped set of records, then run a totals query on that. I'd say it depends on how you have structured your data. I'm seeing a record that gets created when a ticket is opened, and updated when it is closed, not a record for opening and another record for closing the same ticket.

EDIT
Your last post wasn't there when I started.
So, add a calculated field to the query Duration: [Time Out] - [Time In] and total (sum) Duration - again, grouping by person.
Do you really allow spaces in your names?

 

[June7]

And as long as each record does not cross midnight, calculating the elapsed time using the time fields for each should be fairly simple. According to the posted sample, it appears crossing midnight will not be an issue.

If you want to aggregate by some period other than day, use calcs to extract the week, month, year from the date value and build an aggregate query for any of those periods.

 

[Deleted member 147267]

And as long as each record does not cross midnight, calculating the elapsed time using the time fields for each should be fairly simple.

It's not evident in the sample data but if full date/time values were stored, over-midnight calculations would not be a problem. In that case I'd probably use Now() as the time stamp. Values could be formatted to show only time, but that may not be so intuitive down the road when looking at them.

 

[Bullschmidt]

So in summary to kind of put it all together...

To show data like this:
A 3/11/2019 209
A 3/12/2019 169

Could possibly do something like this:

SELECT [UserID], [EntryDate], Sum(DateDiff("N",[TimeIn],[TimeOut])) AS ElapsedMinutes
FROM MyTable
WHERE (([EntryDate]>=#1/11/2019#) AND ([EntryDate]<=#1/12/2019#))
GROUP BY [UserID], [EntryDate]

To show data like this:
A 378

Could possibly do something like this:

SELECT [UserID], Sum(DateDiff("N",[TimeIn],[TimeOut])) AS ElapsedMinutes
FROM MyTable
WHERE (([EntryDate]>=#1/11/2019#) AND ([EntryDate]<=#1/12/2019#))
GROUP BY [UserID]

 

[jlolt]

Thank you for your responses. I tried your query (see below) but it still doubled counted the mins of overlapping tickets.

SELECT [UserID], [EntryDate], Sum(DateDiff("N",[TimeIn],[TimeOut])) AS ElapsedMinutes
FROM MyTable
WHERE (([EntryDate]>=#1/11/2019#) AND ([EntryDate]<=#1/12/2019#))
GROUP BY [UserID], [EntryDate]​

I have attached an excel file where I was able to figure out how to manually calculate the time but, this isn't good solution as I will continually have to manually adjust the ranges. The file may better explain what I'm trying to do. I really appreciate the help. Thanks.