Finding first line of duplicates for deletion

[Tommy B]

Hi Guys,

Hope you can help me out

What I am trying to do is identify duplicate records and delete them from my master table.

I used the wizard to create a duplicates query, it returns the two duplicate records side by side (in actual jfact about 45,000 records side by side!!!)Now what I really need it to do is return just one of the duplicate records as I need to keep one, just removing the duplicate...if that makes sense.

If there were less dupes i could do this manually with my current query by just selecting and removing the offending records...however i am dealing with thousands of them!!!

Is there a way I can get the dupes query to look for dupes and return just one of the records so I can bulk delete them without removing the original record and the duplicate?

Hope this makes sense!!!

Any help is much appreciated

Tommy B

 

[Pat Hartman]

The easiest way to do this if you don't care which record is saved is -
Create a new empty table with the primary key defined so that dupes cannot be added. Then build an append query that selects from the current table and appends to the new table. Once you are sure everything worked properly, delete or rename (if you want to save for a while) the current table and rename the new table to the current table name.

 

[Tommy B]

Excellent! This method works perfectly

Thanks for the help Pat!

Tom.

 

[Rich_Lovina]

Pat Hartman wrote...

Hi there,

Pat wrote: "...The easiest way to do this if you don't care which record is saved is..."

Now, in my dbase I do care, since I have the data set such that the oldest record (by date or older autonumber) appears first in the duplicate set. How do I write the sql for DELETE only the older date (or lower autonumber)?

Or is there a VB solution to the same problem?
Thanks in advance any viewers.

 

[Pat Hartman]

Rich,
Change your sort so that the newest record is first (usually sort descending) if that's the one you want to save.

 

[Rich_Lovina]

Thanks Pat, that was done for manual deletions.