1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Calculating time interval between records of the same table

Discussion in 'MS Access' started by thor2780, Jun 9, 2008.

  1. thor2780

    thor2780 New Member

    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!
     

Share This Page