Query to return most recent date

[monkeysee]

I have a query:

SELECT Inspections.InspectionID, Food.BusinessName,
Food.Priority, Inspections.TypeID, Inspections.InspectorID, Inspections.InspectionDate, IIf([Priority]="1",DateAdd("m",4,[InspectionDate]),IIf([Priority]="2",DateAdd("yyyy",1,[InspectionDate]),DateAdd("yyyy",2,[InspectionDate]))) AS ReInspectionDate, Inspections.FacilityID
FROM Food INNER JOIN Inspections ON Food.FacilityID = Inspections.FacilityID
WHERE (((Inspections.TypeID)=1 Or (Inspections.TypeID)=2))
ORDER BY Inspections.InspectionDate, IIf([Priority]="1",DateAdd("m",4,[InspectionDate]),IIf([Priority]="2",DateAdd("yyyy",1,[InspectionDate]),DateAdd("yyyy",2,[InspectionDate])));


What I want it to do, is to only show the most recent [InspectionDate] for all facilities. Another words, one particular restaurant perhaps named "Deb's #1 Foods" may have several inspection dates over the history of operation. I want a query to list 'all' restaurants in the data base with their most recent inspection date. How can I amend the above query to do so?

Thanks for your help!

 

[PHV]

I want a query to list 'all' restaurants in the data base with their most recent inspection date(/i]
Something like this ?
SELECT Food.BusinessName, Max(Inspections.InspectionDate)
FROM Food INNER JOIN Inspections ON Food.FacilityID = Inspections.FacilityID
GROUP BY Food.BusinessName;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886