Max of Date query (XP/2000)

Topic

New Reply

I’m getting in a pickle with what should be a simple query.

I have a table (tblExams) containing survey info for a number of sites.

An examination (in tblExams) is uniquely defined by its reference number (SiteRef) and exam date (ExamDate). Sites undergo re-examination, the date (DueDate) specified according to a rating (Score). eg Score = 1, DueDate = ExamDate + 10 years, Score = 2, DueDate = ExamDate + 5 years.

The first thing I need to do is create a query containing only the most recent examination for each site (SiteRef) but I don’t seem to be able to do this. In additional to the SiteRef, ExamDate, DueDate and I need to include a few other fields.

Using the query and ‘from date’ and ‘to date’ parameters passed from a form I can then generate a list of exams due to be re-examined during a specific period in the future.

I’m stuck with the query, I’ve tried using Max but my query is still returning all the records in the table and to be honest the I get a bit confused by the options when you use the view ‘Totals’ option in a query .

Reply | Quote

Replies

Hi Darsha,

You need to create two queries.

1) Create a query based on tblExams.
Add only the SiteRef and ExamDate fields to the query grid.
Select View | Totals to change the query to a totals query.
Set the Total option for ExamDate to Max (for SiteRef, it remains the default Group By).
This query returns the most recent ExamDate for each SiteRef.
Store this query as qryMaxDate.

2) Create a query based on tblExams and on qryMaxDate.
Join the tables on SiteRef – SiteRef and on ExamDate – MaxOfExamDate.
Add some or all fields from tblExams to the query grid.
Add calculated fields, if necessary.
Note: this query will not be updateable. You can use it to display records in a form or report, but not to edit records.

Reply | Quote

Perfect

Reply | Quote