Calculating time interval between records of the same table

[thor2780]

I am creating a database for a mining company. There are trucks carrying materials from one place to another. In the database there is the table Trips with the following fields:

Record_ID
Date_ID
Time_ID
Truck_ID
Trip_Count

Each time a truck loads materials a record is created specyfying the record number (Record_ID, auto increment) the current date (Date_ID), the time the truck started loading (Time_ID), the ID of the truck (Truck_ID) and a variable (Number, Trip_Count) which value is usually "1" but at times it can have different values as well. Then another truck comes in, another record is created and then another etc.

Here's how the table looks like:

Record_ID | Date_ID | Time_ID | Truck_ID | Trip_Count
___ 1 ___ | 2/5/08_ | _7:49__ | __ 3 ___ | ___ 1 ____
___ 2 ___ | 2/5/08_ | _7:55__ | __ 4 ___ | ___ 1 ____
___ 3 ___ | 2/5/08_ | _7:57__ | __ 5 ___ | ___ 1 ____
___ 4 ___ | 2/5/08_ | _7:59__ | __ 3 ___ | ___ 1 ____
___ 5 ___ | 2/5/08_ | _8:01__ | __ 4 ___ | ___ 1 ____
___ 6 ___ | 2/5/08_ | _8:02__ | __ 6 ___ | ___ 7 ____
___ 7 ___ | 2/5/08_ | _8:03__ | __ 5 ___ | ___ 1 ____
___ 8 ___ | 2/5/08_ | _8:07__ | __ 3 ___ | ___ 1 ____
___ 9 ___ | 2/5/08_ | _8:10__ | __ 3 ___ | ___ 1 ____
___ 10 __ | 2/5/08_ | _8:16__ | __ 3 ___ | ___ 1 ____
___ 11 __ | 2/5/08_ | _8:20__ | __ 3 ___ | ___ 1 ____
___ 12 __ | 2/5/08_ | _8:24__ | __ 3 ___ | ___ 7 ____
___ 13 __ | 2/5/08_ | _8:27__ | __ 3 ___ | ___ 4 ____

By the end of the day the database table is filled with the information of all the trucks who worked that day along with the time they performed each load. This happens every day.

What I want to do and I don't know whether it is possible with Microsoft Access, is to calculate the interval between two records of the same truck.

For instance, the first interval for the truck with Truck_ID 3 would be 10 minutes in the example above. The interval for truck with Truck_ID 4 would be 6 minutes. The interval for truck with Truck_ID 5 would be 6 minutes. I want to gather all the intervals for a truck and find an average time for each day.

Let's say I want to see all the records for the truck with Truck_ID 3 at 2/5/08 who have a Trip_Count value of 1.
So I create a query using the following SQL code:

SELECT
Trips.Truck_ID,
Trips.Date_ID,
Trips,Time_ID,
Trips,Trip_Count
FROM Trips
WHERE (((Trips.Truck_ID)=3) AND ((Trips.Date_ID)=#2/5/08#) AND ((Trips.Trip_Count)=1));

Here are the results of the query:

Date_ID | Time_ID | Truck_ID | Trip_Count
2/5/08_ | _7:49__ | __ 3 ___ | ___ 1 ____
2/5/08_ | _7:59__ | __ 3 ___ | ___ 1 ____
2/5/08_ | _8:07__ | __ 3 ___ | ___ 1 ____
2/5/08_ | _8:10__ | __ 3 ___ | ___ 1 ____
2/5/08_ | _8:16__ | __ 3 ___ | ___ 1 ____
2/5/08_ | _8:20__ | __ 3 ___ | ___ 1 ____

In order to calculate the time interval between those 6 records I create a query with the following code:

SELECT
A.Truck_ID,
A.Date_ID,
A.Time_ID,
Min(B.Time_ID) AS NextTime,
A.Trip_Count,

FROM Trips AS A LEFT JOIN Trips AS B ON
(A.Truck_ID = B.Truck_ID) AND
(A.Date_ID = B.Date_ID) AND
(A.Time_ID < B.Time_ID)

GROUP BY A.Truck_ID, A.Date_ID, A.Time_ID, A.Trip_Count

HAVING (((A.Truck_ID)=3) AND ((A.Date_ID)=#2/5/08#) AND ((Trip_Count)=1));

And here are the results of this query:

Date_ID | Time_ID | NextTime |Truck_ID | Trip_Count
2/5/08_ | _7:49__ | __7:59__ | __ 3 __ | ___ 1 ____
2/5/08_ | _7:59__ | __8:07__ | __ 3 __ | ___ 1 ____
2/5/08_ | _8:07__ | __8:10__ | __ 3 __ | ___ 1 ____
2/5/08_ | _8:10__ | __8:16__ | __ 3 __ | ___ 1 ____
2/5/08_ | _8:16__ | __8:20__ | __ 3 __ | ___ 1 ____
2/5/08_ | _8:20__ | __8:24__ | __ 3 __ | ___ 1 ____

All I have to do now is subtract the Time_ID field value from the NextTime field value.

Question 1: How do I calculate the interval between the two fields? I try to subtract one field from another using the DateDiff function but it produces really weird results. For example I use the following code:

SELECT
A.Truck_ID,
A.Date_ID,
A.Time_ID,
Min(B.Time_ID) AS NextTime,
A.Trip_Count,
DateDiff("n",[A].[Time_ID],[b].[Time_ID]) AS TimeInterval

FROM Trips AS A LEFT JOIN Trips AS B ON
(A.Truck_ID = B.Truck_ID) AND
(A.Date_ID = B.Date_ID) AND
(A.Time_ID < B.Time_ID)

GROUP BY A.Truck_ID, A.Date_ID, A.Time_ID, A.Trip_Count, DateDiff("n",[A].[Time_ID],[b].[Time_ID]) AS TimeInterval

HAVING (((A.Truck_ID)=3) AND ((A.Date_ID)=#2/5/08#) AND ((Trip_Count)=1));

Question 2: Why does the time value 8:24 appears in the query results (in the final record in the NextTime field) since this value belongs to a record with Trip_Count 7?

Question 3: Can you recommend another method of approaching this issue of calculating the time interval between records of the same table? Can you recommend a different method of calculating the intervals and finding an average?

Well, that's all! I hope you can help!

 

[namliam]

Q1: Should work... alternatively you can do: .[Time_ID]) - [A].[Time_ID]

Q2: Because you are limiting on A.Trip_Count=1, not B

Q3: Unless you happen to use Oracle, no I cannot. Except for what is mentioned at Q1.

Good luck

 

[DCrake]

Simple Software Solutions

This is of the top of my head so don't take it heart it it does not match your needs.

I would add another field to the table call LastTime and when I add a time stamp for the current trip I would get it look for the last entry for the truck on that same day and insert this time here. So you have then got two times on the same plane that you can use to work out the difference.

Having said all that here is a pointers that you may wish to think about.

1. If it is the first trip of the day you will not have a previous time to work out the difference.
2. You are saving your data in short time format. What happens if the trip passes midnight? You start time will be after your finish time!

3. If this will never happen why not store your time in actual mins past minight. Ie 06:00 = 360. So when you come to do your calculations it will be easier to get the desired results.

4. Are you not taking into consideration how long it takes to load and unload?

5. If the start and end points are known why not google the duration and the work of that?

6. Are you takiing into account any delay times experienced by your drivers? How precise are you wanting to be?

Could go on, but this is something to think about.


CodeMaster: