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

deleting duplicate records with only one field different

P: n/a
i have less hair now than i used to...i have a database with many
duplicate records in it with only one exception, there is one field
with the date it was put in the database and that is different. i can
eliminate duplicate records with ease but how do you specify in an sql
statement to delete the one with the earlier date? please help me
before i go bald...

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


P: n/a
Easier to create a dedupe'd table.

Create a GROUP BY query to group by each field except the date field. Choose
Min for the date field.

Change it to a MakeTable query.
There is your dedupe'd table.

(It may be possible to write just the p.k. for the dedupe'd records, and
delete the other records with an outer join.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ross" <rm********@houston.rr.com> wrote in message
news:98**************************@posting.google.c om...
i have less hair now than i used to...i have a database with many
duplicate records in it with only one exception, there is one field
with the date it was put in the database and that is different. i can
eliminate duplicate records with ease but how do you specify in an sql
statement to delete the one with the earlier date? please help me
before i go bald...

thanks

Nov 13 '05 #2

P: n/a
Allen,

you're the best! thanks so much. i feel my folicles tingling. that
worked perfectly. just wish i would have thought of it.

Ross

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@per-qv1-newsreader-01.iinet.net.au>...
Easier to create a dedupe'd table.

Create a GROUP BY query to group by each field except the date field. Choose
Min for the date field.

Change it to a MakeTable query.
There is your dedupe'd table.

(It may be possible to write just the p.k. for the dedupe'd records, and
delete the other records with an outer join.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ross" <rm********@houston.rr.com> wrote in message
news:98**************************@posting.google.c om...
i have less hair now than i used to...i have a database with many
duplicate records in it with only one exception, there is one field
with the date it was put in the database and that is different. i can
eliminate duplicate records with ease but how do you specify in an sql
statement to delete the one with the earlier date? please help me
before i go bald...

thanks

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.