469,270 Members | 1,733 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

deleting duplicate records with only one field different

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
2 3851
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
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.

Similar topics

3 posts views Thread by Nathan Bloom | last post: by
2 posts views Thread by Emmett Power | last post: by
4 posts views Thread by Thomas Arthur Seidel | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.