ACC: Deleting Duplicate Records from Two Tables

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Novice: Requires knowledge of the user interface on single-user computers.

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

MORE INFORMATION

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

Create Two Tables with Duplicate Records

1. Open the sample database Northwind.mdb (or Nwind.mdb in versions 1.x and 2.0).

2. In the Database window, click the Tables tab and 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
   

   Close the TEST1 table.

8. Open the TEST2 table and add the following two new company names:

         Jiffy Ship
         Hurry Package
   

   Close the TEST2 table.

Delete Duplicate Records From One of the Tables

1. Create a new query in Design view based on the TEST1 and TEST2 tables.

2. In Microsoft Access 2.0, 7.0, and 97, delete the join between the ShipperID fields and join the two tables on the CompanyName field (Company Name field in version 2.0).

   In Microsoft Access 1.x, join the tables on the Company Name field.

3. In Microsoft Access 97, perform the following additional steps:

       a. On the Query menu, click Properties.
   

       b. If the Field Properties dialog box appears, click in an empty space
          in the upper half of the query window to display the Query
          Properties dialog box instead.
   

       c. Set the Unique Records property to Yes.
   

4. On the Query menu, click Delete Query (or Delete in Microsoft Access 7.0 and earlier) 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.

5. Drag ShipperID and CompanyName (or Shipper ID and Company Name in versions 1.x and 2.0) from the TEST1 table to the QBE grid. Note that the word "Where" appears on the Delete line in the QBE grid.

6. Drag the * from the TEST2 table to the QBE grid. Note that the word "From" appears on the Delete line in the QBE grid.

7. On the Query menu, click Run.

8. A message appears stating that three rows will be deleted. Click Yes (or OK in versions 1.x and 2.0).

9. Close the query without saving it.

Append the Remaining Records in TEST2 to the TEST1 Table

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 receive a message stating that two rows will be pasted into this table. Click Yes (or OK in versions 1.x and 2.0) to complete the Paste Append.

REFERENCES

For more information about deleting duplicate records, search the Help Index for "deleting records."