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: 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: 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: 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!
