Select the record with the largest value in Field 1 for each ID (1 Viewer)

jeffcullina · May 6, 2002

I have a table that intentionally permits duplicate values for a field called "ID". For each ID, there is a distinct date captured in a field called "DATE". In a query, I would like to select the largest date (most recent) for each ID. Any suggestions on how to do that would be very much appreciated.

Rich · May 6, 2002

Look up Max in help.

jeffcullina · May 6, 2002

I have that part of it. The tricky part is selecting one record for each ID. Suppose I I have 5 records in a table. The first three have an ID of "1", and the second two have an ID of "2". I would like the query to select two records. The first record corrosponds to the record that has an ID value of "1" with the max date, and the second record corresponds to the record with an ID value of "2" with the max date.

Idjit · May 6, 2002

I was just trying to do the same thing myself. What I did is include [ID] in the query as Group By, and [DATE] as Max - and presto, I got the most recent date for each ID.

jeffcullina · May 7, 2002

That is genius. Thanks.

Idjit · May 7, 2002

No problem - it's nice to know I can be useful!

Select the record with the largest value in Field 1 for each ID (1 Viewer)

jeffcullina

New member

Rich

Guest

jeffcullina

New member

Idjit

Clamoring Preschooler

jeffcullina

New member

Idjit

Clamoring Preschooler

Similar threads

Users who are viewing this thread