Delete duplicate records with a query

Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013.

Duplicate data usually sneaks in anytime several users import data to the database at once or if the database wasn’t developed to scan for duplicates. It is quicker to apply a query for the time you prefer to remove much 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

Data deletions cannot be undone by you, so ensure your database is complete prior to you attempting erase duplicate records:

  • Check that the file is not read-only.
  • If you share your database with others, ask them to exit the objects that you seek to work with to evade potential data conflicts.
  • If you have permissions to enter the database in exclusive mode: Select File >Open and pick the database. Select the arrow beside Open and press Open Exclusive.
  • Most significantly, remember to back up your database. The only way to recover erased records is to restore them from a backup. A delete operation may even remove records in associated 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. Pressthe Create tab > Query Design and in the Show Table dialogue box, double-click the table from which you want to delete records.
  2. Double-click the asterisk (*) to append all of the table fields to the query designer.
  3. Insert 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 even 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. Empty the Show checkbox for each criteria field.
  6. On the Design tab, select Run. Confirm that the query returns the records that you want to delete.
  7. Press Design View and on the Design tab, pick Delete. Access modifies the select query to a delete query, covers the Show row in the lower section of the design grid, and creates the Delete row.
  8. One more verification before you run the delete query: Guarantee that the Delete row in the * (all fields) column presents From and Where should be visible in any of the criteria columns.
  9. On the Design tab, choose Run > Yes.

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

Top of Page

Leave a Reply

%d bloggers like this: