CODEWITHSUNDEEP
sqlms-access
user2287689
user2287689

Reputation: 21

Need help finding the next record in access

Here is the table I have

VehicleName EventDate       Message  Landmark
071-314     28-Sep-12 5pm   On Duty 
071-314     28-Sep-12 6pm   Driving 
071-314     28-Sep-12 7pm   On Duty 
071-314     28-Sep-12 8pm   Driving 
071-314     28-Sep-12 3am   Off Duty    
071-315     28-Sep-12 4am   Driving 
071-315     28-Sep-12 5am   On Duty 
071-315     28-Sep-12 6am   Driving 
071-315     28-Sep-12 7am   On Duty 
071-315     28-Sep-12       Off Duty

What I need to do is find the difference from "On Duty" to the next "Driving" or "Off Duty" status. It needs to be seperated by Vehicle name as well.

is this possible with access?

Upvotes: 2

Views: 83

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123484

I put your sample data into a table named [DutyEvents] and tweaked it to be consistent (e.g., the final "Off Duty" for the first [VehicleName] is at 3am the following day)...

VehicleName EventDate            Message
----------- -------------------  -------
071-314     2012-09-28 17:00:00  On Duty
071-314     2012-09-28 18:00:00  Driving
071-314     2012-09-28 19:00:00  On Duty
071-314     2012-09-28 20:00:00  Driving
071-314     2012-09-29 03:00:00  Off Duty
071-315     2012-09-28 04:00:00  Driving
071-315     2012-09-28 05:00:00  On Duty
071-315     2012-09-28 06:00:00  Driving
071-315     2012-09-28 07:00:00  On Duty
071-315     2012-09-28 09:00:00  Off Duty

...then I created the following Access query...

SELECT VehicleName, Message, EventDate AS Start, NextEventDate as End, 
    Round((NextEventDate - EventDate) * 24, 0) AS Hours
FROM
(
    SELECT de.VehicleName, de.EventDate, de.Message,
        (SELECT TOP 1 EventDate FROM DutyEvents 
         WHERE VehicleName = de.VehicleName
            AND EventDate > de.EventDate ORDER BY EventDate
        ) AS NextEventDate
    FROM DutyEvents de
    WHERE de.Message <> "Off Duty"
)

...which produces the following results...

VehicleName Message  Start                End                   Hours
----------- -------  -------------------  -------------------   -----
071-314     On Duty  2012-09-28 17:00:00  2012-09-28 18:00:00   1
071-314     Driving  2012-09-28 18:00:00  2012-09-28 19:00:00   1
071-314     On Duty  2012-09-28 19:00:00  2012-09-28 20:00:00   1
071-314     Driving  2012-09-28 20:00:00  2012-09-29 03:00:00   7
071-315     Driving  2012-09-28 04:00:00  2012-09-28 05:00:00   1
071-315     On Duty  2012-09-28 05:00:00  2012-09-28 06:00:00   1
071-315     Driving  2012-09-28 06:00:00  2012-09-28 07:00:00   1
071-315     On Duty  2012-09-28 07:00:00  2012-09-28 09:00:00   2

Is that what you were looking for?

Upvotes: 1

Related Questions