7/28/2008 3:21:03 PM
Access Tip: Duplicate Records in Two Tables
By Richard Rost
Q: I have two tables with customer information. I need to keep the two tables separate, but I would like to delete the duplicates from one of the tables (i.e. delete the customers from table A who are also in table B)?
A: There's really no easy way to take two tables and delete the duplicate records between them. There is a FIND DUPLICATES query, but that only searches for duplicate RECORDS in a single table... which you could do, if you could merge both tables together and then delete all of the duplicates.
Here's my cheap workaround for finding duplicate records between two tables. This assumes you have a field in them (such as CustomerID) that is the same for both tables.
Step 1: add a field in the table that has the records you want to delete called DeleteMe - or something to that effect.
Step 2: JOIN these two tables together in a query by whatever field is related so that when you run the query you see only records that are in both.
Step 3: convert this query over into an UPDATE query. Now, update the value of the DeleteMe field to YES. See this tutorial for help on Update queries:
Step 4: run the query. Now every record that is in BOTH tables (since they're joined) will be marked YES for DeleteMe.
Step 5. Make a new query based on just the table with records that need to be deleted, and set the criteria so DeleteMe = TRUE.
Step 6. Convert this query over to a DELETE query. This should delete only those records.
If you need more help with ACTION QUERIES (make table, delete, update, append, etc.) I cover them in exquisite detail in my Access 222 Tutorial:
Hope this answers your question.
Keywords: access tips update query delete action