How code this tricky query?

M

mscertified

I have a table of service request (SR) data extracts. There is no unique key.
The extract can be run for any time period and each extract may contain the
same SR number. Each extract record has a run date.
What I need to do is extract each unique SR number for the latest run date
in the file. How do I do this? I guess I must somehow group by SR number and
take the MAX of run date. Any help much appreciated.
 
M

Mauricio Silva

Try this (I didn't test it)

SELECT SRnumber FROM [SR] WHERE SR.RunDate = (SELECT MAX(RunDate) FROM [SR])

Mauricio Silva
 
J

John Vinson

I have a table of service request (SR) data extracts. There is no unique key.
The extract can be run for any time period and each extract may contain the
same SR number. Each extract record has a run date.
What I need to do is extract each unique SR number for the latest run date
in the file. How do I do this? I guess I must somehow group by SR number and
take the MAX of run date. Any help much appreciated.
Click to expand...

A Subquery is the solution here: create a Query based on the table,
and as a criterion on the [RunDate] field put

=(SELECT Max([RunDate]) FROM [SR] AS X
WHERE X.[SRNumber] = [SR].[SRNumber])

using your own table and fieldnames of course.

This query will not be updateable but can be changed into an Append
query, or used as the recordsource for a form or report.

John W. Vinson[MVP]
 
Top