We help IT Professionals succeed at work.

Access 2003 - Calculate Net Time using corresponding records from same table.

Michael Spellman
Michael Spellman used Ask the Experts™
on
I have a table with records representing different activities related to hauling loads and the start/stop times of each.  Activities include actual travel time & stops that occur (such as for fuel, breakdown, etc.)
I want to calculate the actual driving time by subtracting the stop time from the travel time.
The records are related only by the driver and the Date/Time.  So, to subtract the correct stops, I need to selct the stops that have the same driver & fall within the start & stop times of the travel.
I'm not sure of the best way to attack this.  Thanks for the help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Please provide some sample data.
Michael SpellmanSupervisory Operations Support Specialist

Author

Commented:
Using the qryElapsedTime as my data source.
Trucking-Data.mdb
TravelTime-Calc.png
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
OK,

Here is the query I came up with that will identify the duration of the stop and compute an actual TravelTime result.  However, the data you provided is suspect, as in almost every case where you have an enroute stop, the actual travel start/end are within a minute of each other, and the stop start/end times are also within that same 1 minute time span.

This query uses a non-equi join, which means that it cannot be viewed from within the query design grid, only from the SQL view.  What it does is join your TruckData_Master table to itself, based the driver, and the Start/Stop time of the stop being within the Start/Stop time of the travel.  It then computes the difference (EnrouteDuration) as a decimal value and formats that as HH:MM in the StopDuration field.

SELECT TruckData_Master.Driver
            , TruckData_Master.ActType
            , TruckData_Master.StartDateTime
            , TruckData_Master.StopDateTime
            , Enroute.Stop
            , Enroute.ActType AS EnrouteAct
            , [Enroute].[StopDateTime]-[Enroute].[StartDateTime] AS EnrouteDuration
            , Format([Enroute].[StopDateTime]-[Enroute].[StartDateTime], "hh:mm") AS StopDuration
            , Format([TruckData_Master].[StopDateTime]-[TruckData_Master].[StartDateTime]-[EnrouteDuration], "hh:mm") AS TravelTime
FROM TruckData_Master
INNER JOIN TruckData_Master AS Enroute
ON TruckData_Master.Driver = Enroute.Driver
AND TruckData_Master.StartDateTime < Enroute.StartDateTime
AND TruckData_Master.StopDateTime > Enroute.StopDateTime
Michael SpellmanSupervisory Operations Support Specialist

Author

Commented:
That works great!  The data that was in the database was just test data that I entered.  I created a Windows Mobile app that allows drivers to enter the data and sync to an access database.  I was just entering one right after another, hence no real time gaps.
Thanks again for the timely help!