Max Date Qry 1

[monkeysee]

My database contains inspection data for inspections done on food facilities in the area.

My task is to build a report that will show: FacilityID, InspectorID, Last InspectionDate. The following sql works, but when I add the InspectorID, it no longer shows the last inspection date, but all inspections done for each facility.

[highlight #F57900]SELECT Inspections.FacilityID, Max(Inspections.InspectionDate) AS MaxOfInspectionDate
FROM Inspections
GROUP BY Inspections.FacilityID;[/highlight]

What am I missing?

Thank you to all the help that may come my way from all you great gurus!

 

[dhookom]

The simple way is to use two queries. The first is as you have created. Then join your totals query to the Inspections table on FacilityID and InspectionDate. This can be done in a single query but it's not something I want to try with "air code".

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[monkeysee]

Duane,

Awesome!!

Thank you, works like a charm....

 

[Andrzejek]

That also depends on what you mean by "add the InspectorID"?
If you add it to your SELECT portion, did you add it to the GROUP BY portion as well?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson