Duplicate Records - Same Table - Multiple Criteria Fields - Please Help!

[zeke]

Any assistance is much appreciated...

I have a table with duplicate record information and I need to remove
certain records based on values in four different fields. For example:

PK field Name Date1 Date2 Date3
1 Bill 1/21/04 1/18/02 5/14/04
2 Bill 1/15/03 1/18/02 5/14/04
3 Bill 1/25/04 5/14/04

I want to compare the records and keep the record with the most recent
date in field Date3, if the dates match then I want to compare the
dates in field Date2 and if the records match there I want to compare
the dates in field Date1 and delete the one with the oldest dates.
Thanks.

[ByteMyzer]

"zeke" <tu****@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Any assistance is much appreciated...

I have a table with duplicate record information and I need to remove
certain records based on values in four different fields. For example:

PK field Name Date1 Date2 Date3
1 Bill 1/21/04 1/18/02 5/14/04
2 Bill 1/15/03 1/18/02 5/14/04
3 Bill 1/25/04 5/14/04

I want to compare the records and keep the record with the most recent
date in field Date3, if the dates match then I want to compare the
dates in field Date2 and if the records match there I want to compare
the dates in field Date1 and delete the one with the oldest dates.
Thanks.

The action query works on the above table structure and data for a table
named Table1, and should do the trick:

DELETE T1.*
FROM Table1 AS T1
WHERE T1.[PK field] NOT IN
(SELECT TOP 1 T2.[PK field]
FROM Table1 AS T2
WHERE T2.Name=T1.Name
ORDER BY Date3 DESC, Date2 DESC, Date1 DESC;)
;

See if it works for you.

[zeke]

This is excellent. Works perfectly, and much simpler than the
direction I was heading. Thank you!

ByteMyzer wrote:

"zeke" <tu****@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Any assistance is much appreciated...

I have a table with duplicate record information and I need to remove certain records based on values in four different fields. For example:
PK field Name Date1 Date2 Date3
1 Bill 1/21/04 1/18/02 5/14/04
2 Bill 1/15/03 1/18/02 5/14/04
3 Bill 1/25/04 5/14/04

I want to compare the records and keep the record with the most recent date in field Date3, if the dates match then I want to compare the
dates in field Date2 and if the records match there I want to compare the dates in field Date1 and delete the one with the oldest dates.
Thanks.
The action query works on the above table structure and data for a

table named Table1, and should do the trick:

DELETE T1.*
FROM Table1 AS T1
WHERE T1.[PK field] NOT IN
(SELECT TOP 1 T2.[PK field]
FROM Table1 AS T2
WHERE T2.Name=T1.Name
ORDER BY Date3 DESC, Date2 DESC, Date1 DESC;)
;

See if it works for you.