[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],[Β].[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],[Β].[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!