I have a Microsoft Access 2007 report based on a parameterized query which prompts for a start date and end date on run. I need to calculate gaps in time based solely on a field called TimeStamp, and grouped by a field called Trailer.
The start and end date is used in my report to calculate elapsed time and is grouped by a field called Trailer, and results are displayed in the Trailer header. These calculations work just fine. Here are the text controls and their datasource expressions:
The results this returns look like this:
Here's the problem:
Adding the minutes for the two trailers produces 356 minutes, and this is correct. The reason the report returns 396 in the footer is it is looking at the first time stamp on the date, the last time stamp on the date, and ignoring the trailer. (a 40 minute break was taken between the 2 trailers).
The gap comes in when you look at last time on the first trailer and first time on the last trailer:
This information pertains to a warehouse barcode scanning operation as trailers are unloaded, so I need to report on actual times where no work is being done (based solely on when items are scanned which is where I get the time stamp). At no point do I want the warehouse supervisor to have to enter reported break times - he could enter whatever he wants. The time between one trailer and the next gives me a measure of down time. If it's helpful, I have included a screen shot of the report.