Access SQL: Calculating Min date value and duration for each day -

i'm looking @ gps data vehicles, composed of following information:

vehicle        day              ignition   landmark --------------------------------------------------- sample guy 1   7/5/2013 14:32   on          sample guy 1   7/5/2013 15:10   off        random place b sample guy 1   7/5/2013 15:15   on         random place b sample guy 1   7/5/2013 15:20   off sample guy 1   7/20/2013 18:14  on sample guy 1   7/20/2013 18:20  off        random place h sample guy 1   7/20/2013 18:22  on         random place h sample guy 1   7/20/2013 18:24  off sample guy 2   8/1/2013 12:10   on         random place zz sample guy 2   8/1/2013 12:12   on          sample guy 2   8/1/2013 12:15   off        random place zy sample guy 2   8/1/2013 13:10   on         random place zy sample guy 2   8/1/2013 13:15   off 

i'm trying find first ignition off point landmark not null, , last ignition on point landmark not null each day. ideally, calculated field "duration" included display datediff between 2 values.

target output:

    vehicle        day          ignition   landmark   --------------------------------------------------------- sample guy 1   7/5/2013 15:10   off        random place b sample guy 1   7/5/2013 15:15   on         random place b sample guy 1   7/20/2013 18:20  off        random place h sample guy 1   7/20/2013 18:22  on         random place h sample guy 2   8/1/2013 12:15   off        random place zy sample guy 2   8/1/2013 13:10   on         random place zy 

i understand may complex, i'm looking start understanding building blocks completing this. everyone!

first thing must have in gps data table unique primary key. don't think can solve problem without one.

if don't have one, add autonumber field called id gpsdata table

here query work in access once have done that:

select r.resultid,        vehicle,        day,        ignition,        landmark   (select first(id) resultid           (select id,                        vehicle,                        dateserial(day([day]), month([day]), year([day])) normaliseddate,                        ignition                   gpsdata                  ((ignition = "off") , (landmark not null))                 order  day asc)         group  vehicle, normaliseddate, ignition         union         select last(id) resultid           (select id,                        vehicle,                        dateserial(day([day]), month([day]), year([day])) normaliseddate,                        ignition                   gpsdata                  ((ignition = "on") , (landmark not null))                 order  day asc)         group  vehicle, normaliseddate, ignition) r        inner join gpsdata          on r.resultid = gpsdata.id order  vehicle, day  

there may better way of doing this, should work, though it's not trying clever.

basically, split problem smaller chunks re-construct:

• find id of daily records vehicles first 'off'.
  find list, must group data day, must normalise datetime day date part only.

• do exact same thing, last 'on' record of each day.

• union all these 2 lists id (let's call resultid) of records match both first daily 'off' , last daily 'on' of each vehicle.

• re-join list of record ids original gpsdata.