By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,299 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,299 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"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.
Nov 13 '05 #2

P: n/a
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.


Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.