Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2008
    Posts
    21

    Unanswered: Calcullating from previous Record - Query

    Hi,
    I am managing small vehicle fleet operating in a city and I record mileage every week (at any day!) in one MS Access table.

    I want to create a query that compute the difference btn this week's reading and last week's reading of particular vehicle. The table fields are; [DateRecorded], [Vehicle], [VehicleID], [Mileage].

    I also need to have another Query which will show Vehicles whose reading have never been recorded in the last 13days. This is intended to provide the outstanding list of vehicle to make follow up, since no vehicle should be left unrecorded for two weeks.

    Your assistance will be highly appreciated

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This link should help
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    But also because I'm feeling nice I've had a go at it for you
    Code:
    SELECT lowerm.vehicle_id
         , lowerm.date_recorded As [lower_date]
         , upperm.date_recorded As [upper_date]
         , lowerm.mileage       As [lower_mileage]
         , upperm.mileage       As [upper_mileage]
    FROM   mileage As [lowerm]
     LEFT
      JOIN mileage As [upperm]
        ON lowerm.vehicle_id = upperm.vehicle_id
       AND lowerm.date_recorded = (SELECT Max(date_recorded)
                                   FROM   mileage
                                   WHERE  date_recorded < upperm.date_recorded
                                   AND    vehicle_id = upperm.vehicle_id
                                  )
    ORDER
        BY lowerm.vehicle_id
         , lowerm.date_recorded
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Any luck? Keep us informed of how you get on
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2008
    Posts
    21
    I am kind of stuck here, I am trying to figure out how am I going to use the code in my MS Access database. Is it in Query design view, or in a VBA module?
    Please help.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Create a new query, then change from design view to SQL view and you can type away to your hearts content
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2008
    Posts
    21
    I am getting message box saying "Syntax error in expression", when I click Yes it highlight the following line:
    Code:
    ON lowerm.vehicle_id = upperm.vehicle_id
    It highlights just those red characters:

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Pase the full query please
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2008
    Posts
    21
    The complete code is as follow
    Code:
    SELECT lowerm.vehicle_id
         , lowerm.DateRecorded As [lower_date]
         , upperm.DateRecorded As [upper_date]
         , lowerm.Mileage       As [lower_mileage]
         , upperm.Mileage       As [upper_mileage]
    FROM   Mileage As [lowerm]
     LEFT
      JOIN Mileage As [upperm]
        ON lowerm.VehicleId = upperm.VehicleId
       AND lowerm.DateRecorded = (SELECT Max(DateRecorded)
                                   FROM   Mileage
                                   WHERE  DateRecorded < upperm.DateRecorded
                                   AND    VehicleId = upperm.VehicleId
                                  )
    ORDER
        BY lowerm.VehicleId
         , lowerm.DateRecorded

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •