Query to Pull Latest Record

[Chip]

I am trying to build a report from an "Inspection Checklist" table that
contains the annual checklists from each Facility. There can be multiple
Inspection records for each facility but i want to only view the latest
Inspection Record from each facility. I can do this with latest Inspection
Date or I can use the largest Inspection ID# (this is the Primary Key of the
table which is an 'Autonumber' field that increases for each Inspection
inputted.) Is there any way to pull this type of query in ACCESS??

I am just learning ACCESS and can barely spell 'VB' so please be gentle with
your answers...

And as always THANKS IN ADVANCE!!!!

 

[Tom Ellison]

Dear Chip:

The best way is to use a column containing the Inspection date or
date/time.

SELECT Facility, OtherData, InspectionDate
FROM YourTable T
WHERE InspectionDate = (SELECT MAX(InspectionDate) FROM YourTable T1
WHERE T1.Facility = T.Facility)

The subquery (the stuff after the "=") finds the most recent
InspectionDate for each Facility. If you have two rows with the same
InspectionDate for the same Facility, and that is the most recent,
then you could still get two rows for that facility.

If you want to study this, the technique is usually called "correlated
subquery."

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts