ACC2000: How to Delete Duplicate Records in Two Tables

This article shows you how to delete duplicate records from two tables and to retain the most current information (records). In addition, the article shows you how to merge the results of these two tables into one table.

This example walks you through creating two tables, TEST1 and TEST2, which have duplicate records. After you add additional non-duplicate records to both tables, you delete the duplicate records from the TEST2 table. Finally, you update the TEST1 table to include non-duplicate information from the TEST2 table.

Create Two Tables with Duplicate Records

To create two tables with duplicate records, follow these steps:

1. Open the sample database Northwind.mdb.
2. In the Database window, click the Tables tab, and then select the Shippers table.
3. On the Edit menu, click Copy.
4. On the Edit menu, click Paste.
5. In the Paste Table As dialog box, type TEST1 in the Table Name field, and then click OK.
6. Repeat steps 4 and 5, this time pasting the table as TEST2.
7. Open the TEST1 table and add the following two additional company names:
   ABC Shipping
   XYZ Express
8. Close the TEST1 table.
9. Open the TEST2 table and add the following two new company names:
   Jiffy Ship
   Hurry Package
10. Close the TEST2 table.

Delete Duplicate Records from One of the Tables

To delete duplicate records from one of the tables, follow these steps:

1. Create a new query in Design view based on the TEST1 and TEST2 tables.
2. Delete the join between the ShipperID fields, and then join the two tables on the CompanyName field.
3. Right-click inside the Query window and select Properties.
4. Set the Unique Records property to Yes.
5. On the Query menu, click Delete Query to change the query to a Delete query. In this example, the TEST1 table is considered the most current information; therefore, duplicate information is deleted from the TEST2 table.
6. Drag ShipperID and CompanyName from the TEST1 table to the QBE grid. Note that the word "Where" appears on the Delete line in the QBE grid.
7. Drag the * from the TEST2 table to the QBE grid. Note that the word "From" appears on the Delete line in the QBE grid.
8. On the Query menu, click Run.
9. A message appears stating that three rows will be deleted. Click Yes.
10. Close the query without saving it.

Append the Remaining Records in TEST2 to the TEST1 Table

To append the remaining records in TEST2 to the TEST1 table, follow these steps:

1. Open the TEST2 table.
   
   Note that the only two records remaining in the table are the non-duplicate records you added earlier. All the duplicate records were deleted.
2. On the Edit menu, click Select All Records.
3. On the Edit menu, click Copy.
4. Close the TEST2 table and open the TEST1 table.
5. On the Edit menu, click Paste Append. You will receive a message stating that two rows will be pasted into this table. Click Yes to complete the Paste Append.

Your most current information, without duplicates, is now in one table (TEST1).

For more information about deleting duplicate records, click Microsoft Access Help on the Help menu, type deleting duplicate records in the Office Assistant or the Answer Wizard, and then click Search to view the topic.