Delete duplicate records with a query

Duplicate data often creeps in when multiple users add data to the database at the same time or if the database wasn't designed to check for duplicates. It is easier to use a query when you want to delete a lot of duplicate data. The first step is to Find duplicate records with a query in your database.

Note: The methods described in this article do not apply to Access web apps.

Before deleting any data

You can't undo data deletions, so make sure your database is ready before you try to delete duplicate records:

• Make sure that the file is not read-only.

• If you share your database with others, ask them to close the objects that you want to work with to avoid possible data conflicts.

• If you have permissions to open the database in exclusive mode: Click File >Openand select the database. Click the arrow next to Open and click Open Exclusive.

• Most importantly, remember to back up your database. The only way to recover deleted records is to restore them from a backup. A delete operation might also delete records in related tables.

Note:  To restore from a backup, close and rename the original file so that the backup copy can use the name of the original version. Assign the name of the original version to the backup copy, and open it in Access.

Create and run a delete query

1. Click the Create tab > Query Design and in the Show Table dialog box, double-click the table from which you want to delete records.

2. Double-click the asterisk (*) to add all of the table fields to the query designer.

3. Add the fields that you will use to identify the records for deletion. For example, suppose a customer goes out of business and you need to delete all pending orders for that customer. To find just those records, you can add the Customer ID and Order Date fields to the design grid.

4. You can also add criteria in the Criteria row. For example, you might add the Customer ID of the customer that went out of business and the date after which that customer's orders are invalid.

5. Clear the Show check box for each criteria field.

6. On the Design tab, click Run. Verify that the query returns the records that you want to delete.

7. Click Design View and on the Design tab, click Delete. Access changes the select query to a delete query, hides the Show row in the lower section of the design grid, and adds the Delete row.

8. One more verification before you run the delete query: Make sure that the Delete row in the * (all fields) column displays From and Where should appear in any of the criteria columns.

9. On the Design tab, click Run > Yes.

For more, see Compare two tables and find records without matches.

Top of Page