Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gmmastros on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Max Date Qry 1

Status
Not open for further replies.

monkeysee

Programmer
Sep 24, 2002
201
US
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!

 
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
 
Duane,

Awesome!!

Thank you, works like a charm....

 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top