Calculate Total Gaps In Elapsed Time

goneal
goneal used Ask the Experts™
on
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:

====Trailer Header====
StartTime: =Min([TimeStamp])
StopTime: =Max([TimeStamp])
txtMinutes: =Round(DateDiff("n",[StartTime],[StopTime]),2)

The results this returns look like this:

**TRAILER HEADER**
Trailer: 725929
Start: 12:02
Stop: 15:09
Minutes: 179
==========
Trailer: 775002
Start: 15:41
Stop: 18:38
Minutes: 177
==========
REPORT FOOTER
Start: 12:02
Stop: 18:38
Minutes: 396

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:
Stop: 15:09
Start: 15:41

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.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Try this:

JeffCoachman
Access-EEQ-24468693ReportCalcula.mdb

Author

Commented:
Too cool! Thanks Jeff - we are getting really close.
The difficulty I see is there may be times when there will be 3 or more trailers with gaps between each. When I add the below records to the dataset you sent, it only calculates the gap between the first two trailers.
TrailorID      StartDateTime      EndDateTime
3      6/1/2009 4:43:00 PM      6/1/2009 5:43:00 PM
4      6/1/2009 5:53:00 PM      6/1/2009 6:53:00 PM

This is probably due to the format event for the report footer only being called once. How would we get it to pick up the other gaps? (between 2 and 3, and between 3 and 4)? Should I try moving the routine to the trailer header? I'm guessing it gets formatted for each time there is an instance of a new trailer. What I'm not sure about is how to get the lngGapMins variable to not expire (ie to hold its value between calls of the format event). Am I off track?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:

Well......

You only posted a 2 trailer example.

Then you stated:
<Adding the minutes for the two trailers produces 356 minutes>
...then...
< a 40 minute break was taken between the 2 trailers?>

Further reinforcing my belief that this was to be a "Two" trailer report.
;-)

Adapting this for 2 or more trailers may be difficult, depending on the exact Report Layout you need.

Can you post an example of *exactly* what a 3-4 trailer report would look like?
(In your original post you stated that you had included a screenshot, but I cannot locate it)

Thanks

JeffCoachman
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Sorry about that -
I should have allowed for the possibility that there would be more than two trailers, and I did attempt an upload and thought it had occurred, but I see I'm mistaken. :)

In any case, the attached screen shot shows only two trailers (if I can get it to upload) because I have yet to receive more than this, but the db is brand new and has only had one day of operation. This morning, we are expecting three trailers.

There is only one spot for reporting of break (gap) time, and that is as a grand total in the report footer since I have not been asked to capture it more granularly. If we can get it to recurse by the trailer groupings and maintain the value between the function calls, we should be golden.

Also, you should know that I'm not calculating the date difference in the query but in the report, and I'm using the header of the trailer field for grouping, so I have no detail section on the report.

Have you any idea why the .JPG screen capture won't upload? It's only 45K in size, so it's below the 50 MB ceiling. When I click attach file, browse for it, and click open, I get the web page message that it's uploading and verifying contents, but then nothing happens. No rejection message, and no screen shot uploads.

Do you need full table, query and report specs for this, and can we work with the above, assuming the screen capture is present once I submit (but I doubt it is since I see no indication that it's here.)

Thanks again for your help so far!
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
1. Sometimes the file won't upload if you have it still open in the graphics program.

2. All I need is and example of how a 3 trailer report will look.
You can draw it or post it as ascii, but I need to see the layout, so the change for ambiguity is minimized.

Author

Commented:
Figured out why it wouldn't upload: Internet Explorer 8.0 issue. Switched to compatibility mode, and now it uploads.

Hope this helps.
Report.JPG
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
OK,

I'll go over this tonight
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Try this:

JeffCoachman
Access-EEQ-24468693TrailerTrailo.mdb

Author

Commented:
Cool - though I'm afraid I may have misled you a bit when I posted this:
TrailorID      StartDateTime      EndDateTime
3      6/1/2009 4:43:00 PM      6/1/2009 5:43:00 PM
4      6/1/2009 5:53:00 PM      6/1/2009 6:53:00 PM

I assumed we were on the same page from my original problem description:
"I have a Microsoft Access 2007 report based on a <b>parameterized query which prompts for a start date and end date on run.</b> I need to calculate gaps in time based solely on a field called TimeStamp, and grouped by a field called Trailer."

This works well in the database you sent me, butI didn't look it over your solution closely enough to realize that it depends on having start and end fields in my table. There are just time stamps which I <b>query</b> to give me start and end times.

Here are specs for the table containing the time stamps, the table containing the trailer information and the query which feeds my report:
======TABLE DEF: LOADS=====
ScanID: AutuNumber, Primary Key
Scan: Text, (contains the barcodes which are scanned)
TrailerID: Foreign Key from Trailers table
TimeStamp: Date/Time, Defaults to Now() and inserts when scan trigger is pulled

=====TABLE DEF: TRAILERS=====
TrailerID: Autonumber, primary key
Trailer: text
Manifest, text
ProNumber, text
Employees, Number
Cube, Number
Comments, Text
HAZMAT, Yes/No

Here is the SQL for the query:
SELECT Trailers.Trailer, Trailers.Employees, Loads.TimeStamp, Trailers.Manifest, Trailers.ProNumber
FROM Trailers INNER JOIN Loads ON Trailers.TrailerID = Loads.TrailerID
WHERE (((Loads.TimeStamp) Between [Start date] And [End date]));

I did try tweaking the code to refer to the query which generates my report, but I'm being prompted for two parameters for the open recordset.

I also tried feeding the SQL string which generates the query with the same result.

The expected parameters I would think are Name, Type,Options, and LockEdit, but nothing seems to make a difference. I even tried supplying the parameter prompts [Start Time] and [End Time].

If need be, I can try attaching a copy of the database itself, but I will have to sanitize it to remove any reference to the business whose data it's contains.
'first tweak: feed an SQL string as the recordset
strSQL = "SELECT LoadTimes.Trailer, Min(LoadTimes.TimeStamp) AS [First], " _
& "Max(LoadTimes.TimeStamp) AS [Last] FROM LoadTimes GROUP BY LoadTimes.Trailer;"
 
 
Set rst = CurrentDb.OpenRecordset(strSQL)
 
'second tweak: refer to the query by name
Set rst = CurrentDb.OpenRecordset("LoadTimes")

Open in new window

MIS Liason
Most Valuable Expert 2012
Commented:
<it depends on having start and end fields in my table. There are just time stamps which I <b>query</b> to give me start and end times.>

Ok, so why can't the stamps *Be* stored in the table?
Then it all becomes simple.

Besides, if the you are querying these stamps, then they must be in a table somewhere?

Yes, post a sample of your DB, and I will take a look.

Author

Commented:
Thanks again for your work on this.

Here are the tables, query, and report of interest:
==TABLES==
Trailers, Loads

==QUERY==
LoadTimes

==REPORT==
LaborHoursbyProNumber

The TimeStamp fields is in the Loads table. There are 3500 or so of them, and each has an associated trailer which is why I parameterized the query which feeds the report. Anyhow, I'm sure the actual database (sanitized to remove references to business names) will be much more helpful than my multitude of words... ;)

Let me know if there's anything else you need me to carify.
Sample.mdb
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
I'll take a look at thos tomorrow

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial