ACC: Deleting Duplicate Records from Two Tables

ID: Q98664

The information in this article applies to:

SUMMARY

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

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.


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:


    1. On the Query menu, click Properties.


    2. 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.


    3. 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.


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


REFERENCES

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

Additional query words: dup remove 


Keywords          : kbusage TblModfy 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbinfo

Last Reviewed: March 23, 1999