How to leave the biggest date range within a certain date range

[iren]

Hi everyone!

I have a multiple admission records for the same members. I need to delete those records for the same member whose discharge date minus Admit_dt is the biggest within the time range and to leave just those ones whose (disch-adm_dt) date range within a certain date range is bigger for the dame admID and facility. Also all full duplicates should be eliminated
For example:
Fil has five records

admId name facility adm-dt disch
111 Fil A 12/18/2002 1/7/2003
111 Fil B 12/18/2002 1/2/2003
111 Fil B 12/18/2002 1/7/2003
111 Fil B 12/18/2002 1/4/2003
111 Fil B 12/18/2002 1/7/2003


the output should look:

admId name facility adm-dt disch
111 Fil A 12/18/2002 1/7/2003
111 Fil B 12/18/2002 1/7/2003


How can I implement it in Access query?

Thank you in advance!

Iren

 

[SkipVought]

Hi,

Use the MAX function on disch and Group on the others.

Skip,

_[red][/red]