How code this tricky query ?

[ZABADAK]

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.

 

[dhookom]

Do you have some sample records (significant fields only) and what you would like to query from the sample records? How about real table and field names?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ZABADAK]

OK, here goes:
-------------
Run Date 1/1/05
From 12/1/04
To 12/31/04
SR 000
Data X
-------------
Run Date 1/1/05
From 12/1/04
To 12/31/04
SR 123
Data X
-------------
Run Date 2/1/05
From 1/1/05
To 1/31/05
SR 123
Data Y
-------------
Run Date 2/1/05
From 1/1/05
To 1/31/05
SR 999
Data Z
-------------

The above is a vastly oversimplified example of data. Two extracts have been run one on 1/1/05 covering December and one on 2/1/05 covering January.
SR 123 existed in both runs, so I only need the latest data from the run on 2/1/05. SR 000 only existed in the first extract so I need that record.
SR 999 only existed in second extract so I need that record.

 

[traingamer]

If this is all you need, you pretty much summed it up in your request:

Select SR, Max([Run Date])
from table
group by SR

traingamer