ACC: How to Delete Duplicate Records from a Table

Article translations Article translations
Article ID: 109329 - View products that this article applies to.
This article was previously published under Q109329
Novice: Requires knowledge of the user interface on single-user computers.

Expand all | Collapse all

SUMMARY

This article describes how you can remove duplicate records from a table by using primary keys. By using this method, you can check for duplicate values in up to 10 fields in the table.

MORE INFORMATION

To remove duplicate records from a table, follow these steps:
  1. Make a copy of the structure of the table from which you want to remove the duplicate records.

    To copy a table:

    1. Select the table in the Database window,
    2. Click Copy on the Edit menu.
    3. Click Paste on the Edit menu.
    4. Enter a new name for the table.
    5. Select Structure Only
    6. Click OK.
  2. Open the new table in Design view, and select the field(s) that you want to check for duplicates. To make your selection the primary key for the table, click the Primary Key button on the toolbar.
  3. Save and close the table.
  4. Create an append query, and append the data from the original table to the new table. Because the primary key fields in the new table will not permit duplicates, you receive the following error message:

    In Microsoft Access 7.0 and 97:
    Microsoft Access can't append all the records in the append query.
    In earlier versions:
    Errors were encountered...Proceed anyway?
    Click OK. The new table will contain the data from the original table, but will not have duplicates.
NOTE: A fast way to remove duplicate records from a small table is to select the table in the Database window, click Copy on the Edit menu, and then click Paste on the Edit menu. In the Paste Table As dialog box, type the name of the table created in Step 1 in the Table Name box, and select the Append Data To Existing Table button. When you click OK, the records will be appended from the original table, without the duplicate records.

REFERENCES

For more information about deleting duplicate records, type "delete duplicate records" in the Microsoft Access 97 Office Assistant, click Search, and then click to view "Automatically delete duplicate records from a table."

Properties

Article ID: 109329 - Last Review: January 18, 2007 - Revision: 2.1
APPLIES TO
  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition
Keywords: 
kbfaq kbhowto kbusage KB109329
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com