none
MS Access SQL Question RRS feed

  • Question

  • Good Afternoon,

    I am working on a database for someone that tracks the vehicles they use in their business.  Due to vehicles having new license plates issued to them as they expire the business wants to track the current plate for each vehicle as well as all plates that were previously issued to each vehicle.

    I have created a table for vehicles, tbl_vehicles.

    I have also created a table for license plates, tbl_license_plates.

     Being that each vehicle has multiple license plate records.  I need on a form, frm_vehicles, (where certain data for each vehicle is updated) to show only the most recent license plate number to appear.  This needs to happen even if the plate has expired...i.e...is no longer valid.

    The problem that I am encountering is that I do not have sufficient SQL skills to construct a query that returns for each vehicle only the most recent plate.  I wrote the below query and it returns the vehicle_master_id and the expiration date of the most recently issued plate.

     

     However, when I try to add the license_plate_number to the query, it returns every plate that has every been issued for each vehicle.  This is a problem because I need the query to return only the most recently issued plate, whether or not it is valid (unexpired).  

    So, the guidance that I am seeking is how to construct this query so that it returns the license_plate_number for only the most recently issued plate, regardless of validity. 

    Can someone point me in the right direction, please?

    Saturday, October 19, 2019 7:04 PM

Answers

  • Restrict the query by a subquery, correlated with the outer query on vehicle_master_id, which returns the latest date_issued value for the vehicle:

    SELECT tbl_vehicles.vehicle_master_id, vehicle_name, license_plate_number, date_expires
    FROM tbl_vehicles.vehicle_master_id INNER JOIN tbl_license_plates
    ON tbl_vehicles.vehicle_master_id = tbl_license_plates.vehicle_master_id
    WHERE date_issued =
        (SELECT MAX(date_issued)
         FROM tbl_license_plates
         WHERE tbl_license_plates.vehicle_master_id =  tbl_vehicles.vehicle_master_id);


    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Sunday, October 20, 2019 12:09 PM Column added to SELECT clause
    • Marked as answer by Forgivenbygrace Wednesday, October 23, 2019 2:01 AM
    Sunday, October 20, 2019 11:59 AM

All replies

  • SELECT Vehicle,License_Plate_Number Max(date_issued) AS MaxOfdateissued
    FROM yourTable
    GROUP BY Vehicle,License_Plate_Number ;

    If you need the license_plate_master_id ...then you need to Join the above query to the table (use both plate & MaxOfDateIssued as joined fields)

    Saturday, October 19, 2019 7:50 PM
  • Mr. Tsioumpris,

    Thank you for such a quick response.  I tried your recommendation and run into the same issue.  I get the expiration date of all plates for all vehicles. 

    I need it to return only one license plate per vehicle.  From the above example, I would like for it to return only the first and third record.

    Saturday, October 19, 2019 8:07 PM
  • You will need to construct a separate query that shows for each vehicle_master_id, the MaxOfExpiration_Date (using TOTALS). Then once you have those, you can add that query to the query you have with a vehicle_master_id one-to-one relationship. Just add the MaxOfExpiration_Date field to the query you already have. But you need to capture the latest Expiration_Date first in a separate query.
    Saturday, October 19, 2019 9:50 PM
  • Restrict the query by a subquery, correlated with the outer query on vehicle_master_id, which returns the latest date_issued value for the vehicle:

    SELECT tbl_vehicles.vehicle_master_id, vehicle_name, license_plate_number, date_expires
    FROM tbl_vehicles.vehicle_master_id INNER JOIN tbl_license_plates
    ON tbl_vehicles.vehicle_master_id = tbl_license_plates.vehicle_master_id
    WHERE date_issued =
        (SELECT MAX(date_issued)
         FROM tbl_license_plates
         WHERE tbl_license_plates.vehicle_master_id =  tbl_vehicles.vehicle_master_id);


    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Sunday, October 20, 2019 12:09 PM Column added to SELECT clause
    • Marked as answer by Forgivenbygrace Wednesday, October 23, 2019 2:01 AM
    Sunday, October 20, 2019 11:59 AM
  • You can filter by a subquery:

    SELECT
        tbl_vehicles.vehicle_master_id, 
        tbl_vehicles.vehicle_name, 
        tbl_license_plates.license_plate_number, 
        tbl_license_plates.date_expires
    FROM 
        tbl_vehicles 
    INNER JOIN 
        tbl_license_plates 
        ON tbl_vehicles.vehicle_master_id = tbl_license_plates.vehicle_master_id
    WHERE 
        tbl_license_plates.date_expires=
            (Select Top 1 date_expires 
            From [tbl_license_plates] As T 
            Where T.vehicle_master_id = tbl_vehicles.[vehicle_master_id] 
            Order By T.date_expires Desc);


    Gustav Brock

    Sunday, October 20, 2019 12:03 PM
  • I just noticed that you already have a 'valid' field in the tbl_license-plates table that indicates the license plate last valid date. Just filter your records WHERE [tbl_license_plates]![valid]=-1 (True)
    Sunday, October 20, 2019 2:39 PM
  • I just noticed that you already have a 'valid' field in the tbl_license-plates table that indicates the license plate last valid date. Just filter your records WHERE [tbl_license_plates]![valid]=-1 (True)

    Not quite enough:

    show only the most recent license plate number to appear.  
    This needs to happen even if the plate has expired...i.e...is no longer valid.


    Gustav Brock

    Sunday, October 20, 2019 3:57 PM
  • Thank You Mr. Sheridan. This seems to have done the job.
    Wednesday, October 23, 2019 2:01 AM