Deleting Duplicate Records (1 Viewer)

I apologize if this has been asked and ans before. But i am a little frustrated now. I have a data base with a primary key but some records have been added from a excel data base and now the data base has duplicate records with different primary keys. I do a Query looking for duplicate records based on a field not the the primary key. There are 315 out of 22000. I can sort the table and then manually delete the dups. The Query does not allow deleting records so finding the culprits in the full Data base is time consuming. How can i delete the duplicate records using a query? Is this function available hidden someplace in the guts of Access? Seems like this would be a frequent problem. The Microsoft forum addresses this problem and the only viable solution is the manual method, when they get to the Query method it looks like they just forgot what the subject was and tell you how to delete one record if you know what the criteria is. If two non Key fields are Identical then i want to delete one of the records.

There's not a lot of info about how to handle that multiple duplicate situation because it's extreme. You want to check for duplicates before you create or import them -- because deleting duplicates is so complicated. And users might have now attached other data to the many dupes, so automating deletion becomes very risky that you'll lose information. Maybe one dupe has an address you need and another dupe has a couple of contacts you need. Tricky to automate.

I think of data in some ways like it's a tank of pure water. What you want to do is filter out the problems before you allow them into your tank. If you already have impurities--dupes--in your tank, start a new tank. Import stuff in line-by-line and don't allow dupes at that time. If dupes are encountered, import all their child data, and then delete them.

hth

You should make an aggregate query from your table. Bring down and GROUP BY every field that should be unique, then bring down the ID field and make it show the MIN. That query will show all the 'unique' IDs you want. Save it and call it 'UniqueIDs'.

Next,create a new query bringing in your table and the UniqueIDs query , link them by ID. Now you have a few options:

1. Make a new table with just the 'unique' records: In that new query, bring down all the fields from your table. Turn that into a Make-Table query and you have a new table with just the records you want.

2. Delete the 'non-unique' records: In that new query, make it a LEFT JOIN from your table to UniqueIDs. Bring down the ID field from both sources and put criteria underneath the UniqueID.ID of 'Null'. Turn that into a Make-Table query and you have a table with the records you want to delete. In a new query, bring in your initial table and the one you just created, link them by ID and delete the ones in the main table. Your main table will now have just the 'unique' records you want.

Thanks for the quick reply's. I get the picture that you make sure you do not have a lot of dups. As it turns out I imported one month worth of Excel data twice. I now am deleting all data for August and reimporting it one time Of the other 22000 records only had 6 dups so deleted those manually.