Querying for the Last Records

[Helga]

My database holds info on fleet. I have a query that retrieves info on each
fleet record from several tables between specified dates entered by the user.
The problem I am having is that some of these tables have multiple records
for each fleet record between the specified dates. I only want the most
recent or last entered records for each fleet from my Registration table.
Any Advise.

Here is the current query:
SELECT tblVehicle.vehFleetIDNum, tblVehicle.vehYear, tblVehicle.vehMake,
tblVehicle.vehModel, tblVehicle.vehLicensePlateNum, tblCity.citName,
tblRegistration.regExpirationDate, tblOwner.ownName, tblLeasedTo.leaName,
tblRegistration.regType
FROM (tblLeasedTo INNER JOIN (tblCity INNER JOIN (tblOwner INNER JOIN
tblVehicle ON (tblOwner.ownIDNum = tblVehicle.vehOwnerID) AND
(tblOwner.ownIDNum = tblVehicle.vehOwnerID)) ON tblCity.citID =
tblVehicle.vehLocation) ON tblLeasedTo.leaID = tblVehicle.vehLeasedTo) INNER
JOIN tblRegistration ON tblVehicle.vehVehicleIDNum =
tblRegistration.regVehicleIDNum
WHERE (((tblRegistration.regExpirationDate) Between [Enter AS OF expiration
date:] And [Enter LATEST desired expiration date:]) AND
((tblVehicle.vehSold)=False));

Thank you.

 

[Jeff Boyce]

Helga

The concept of "last" in an Access database is somewhat fuzzy. You will NOT
want to use the Last() function provided by Access, since Access determines
in what order it internally stores records, and uses this for Last().

You will need a date field (and it sounds like you have one), and you will
want to use the Max() function against that date -- you are looking for the
maximum date (most recent date).

I would probably approach this problem by first determining the maximum date
for the grouped records, then building a second query to fill in the data.
I'm quite confident that others more conversant in SQL can provide a more
elegant, one-step solution.