Deleting duplicate records

[dawnd3]

Hi there, I have a table with over 30,000 records, but many are duplicates. I did a "find duplicates" query and then created a query that grouped the duplicates so that I had a list of the ones I wanted to delete. I then created a delete query where I joined the duplicate results up with original table. I put the asterick in the FROM column and then in the Where column I put tblContact![ContactID]=qryDuplicates![ContactID]

I have tried to change the syntax and the format of the query, to no avail. I keep getting different messages, the most common is "cannot delete from the specified table". This table is the child in all the relationships and I have no problem deleting from it otherwise. I think I have just set up this delete query wrong. Can anyone help? Is there a better way to delete duplicates?

Dawn

http://www.calliopeconsulting.net

 

[leobaby]

I saw an example somewhere in which you created a query with the duplicate records and joined (via the duplicate field) it to another query that used the min total (remember view totals) to grab the first (min) record of every duplicate and then ran that through a delete query.

LB

 

[dawnd3]

The problem is, the delete query doesn't seem to like it when I use a query with totals as the record source. Your example doesn't seem too far off from what I tried. Do you know how the delete query was set up? Maybe that is where I am getting it wrong. Thanks. Dawn

http://www.calliopeconsulting.net

 

[leobaby]

Okay, I found the example and it is best described with a jpeg

http://www.leobaby.com/misc/AccessDeleteQueryExample.jpg

I hope this helps.

 

[doile]

I have this problem from time to time. Instead of trying to delete dups with a query, I copy the table structure (Copy - structure only), modify it with index, no dups on the field with dups and then append the table with the dups into the new table. Then change the names of the old table and then rename the new table to the old table name. This may not be the best way, but works for me.