How to write specific query efficiently

[thebigthing313]

So I'm kind of stumped on how to properly write this query.



I have three tables involved:

tblInspectionSites - 0:M - tblInspections - 0:M tblTreatments

I am trying to write a query to report the details of the latest treatment(s) for each inspection site (if any). "Latest" is determined by a timestamp found in tblInspections.

I am trying to accomplish at least finding a way to get the latest inspection which would make getting the latest treatment details easy.

I am able to get the latest date of inspection using a MAX aggregate on the Timestamp, but how do I get the primary key(s) of those inspections with that latest date?

Thanks in advance,
Adrian

[June7]

A TOP N nested query might get you what you want http://allenbrowne.com/subquery-01.html#TopN.

Another technique involves domain aggregate functions, which can be slow performers in query.

[NTC]

make an Aggregate Query of tblInspections grouping on the Site Key field (or whatever you call it) and MAX on the date stamp; save that as 'LastInspectQ'

then make a Select Query; with tblInspections and LastInspectQ joining the 2 fields both Site Key field and the TimeStamps fields; this query will result in just the tblInspections records that match - add all the fields you want. Save this as 'LastInspectQ2'....

then use this 'LastInspectQ2' as the basis for finalizing what ever other new query involving the other tables is needed for your report.

[thebigthing313]

Thank you both for the responses, after some last minute guesses before the week ended on Friday, I stumbled upon NTC's solution on my own. But thank you anyway June7, I still found that post informative.