Others

How to Find and Delete Duplicate Records in Microsoft Access

Maira Mehtab Sep 24, 2026 870 views

Question details

The user needs to identify and delete duplicate records in a database and set up preventative measures to avoid future duplicates.

Product: Microsoft Access
Device & OS: not provided
Scenario: Managing a database where redundant data entries exist and need to be safely cleaned up without causing unintended data loss.
Observed behavior: Duplicate records are present in the tables, requiring manual or query-based intervention to remove them and enforce unique data entry.

Before you start

Always create a backup copy of your database before running any delete queries or making bulk modifications to prevent permanent, accidental data loss.

Solution 1Recommended

Use Select and Delete Queries to Safely Remove Duplicates

Create a select query to review the duplicate records before converting it into a delete query to safely remove them from your database.

This method is the safest way to perform bulk deletions because it allows you to preview exactly which records will be affected before any permanent changes are made.

Create a Select Query

Navigate to the Create tab, click 'Query Design', add your target table, and insert the fields used to identify duplicates, such as date and time columns.

Review the Duplicates

Run the select query to view the results. Carefully check the data to confirm that these are the exact duplicate records you intend to remove.

Convert to a Delete Query

Switch back to Design View. In the Query Type group on the ribbon, click 'Delete' to convert your select query into a delete query. Set the criteria to specify which versions of the duplicates to remove.

Run the Delete Query

Click the 'Run' button. Microsoft Access will prompt you with a warning about the number of rows that will be deleted. Confirm to permanently delete the duplicates.

Safety First: Deleted records cannot be recovered via an Undo command. Always ensure your backup is up to date before running the delete query.

Solution 2

Sort and Manually Delete Records

Sort the table records by entry order or specific matching fields to manually identify and delete incorrect duplicate entries.

Solution 3

Prevent Future Duplicates with a Unique Index

Apply a unique index to specific fields, such as exact date and time, to prevent users from entering duplicate data in the future.

Free Microsoft Office alternative

Looking for a Lightweight Microsoft Office Alternative?

While WPS Office does not include a direct database management tool like Access, it is an exceptional, free alternative for managing spreadsheet data, documents, and presentations. You can easily export your database tables to Excel or CSV formats and use WPS Spreadsheet's built-in duplicate removal tools.

1. Download WPS Office: Get WPS Office for free from the official website and install it on your device.
2. Open Your Exported Data: Export your Access table as an .xlsx or .csv file and open it using WPS Spreadsheet.
3. Manage Duplicates Easily: Navigate to the Data tab and click 'Remove Duplicates' to clean your dataset instantly.

Fully compatible with Microsoft Excel, Word, and PowerPoint file formats.Includes built-in 'Highlight Duplicates' and 'Remove Duplicates' features in Spreadsheets.Lightweight, fast, and consumes significantly fewer system resources.Familiar user interface makes migrating from Microsoft Office seamless.

microsoft office alternative - wps office

Frequently Asked Questions

Can I undo a delete query in Microsoft Access?

No, once a delete query is executed, the records are permanently removed from the database and cannot be undone. This is why it is critical to test the query on a database copy or use a select query to preview the data first.

Why do I get an error when creating a unique index?

This error occurs if the field you are trying to index already contains duplicate values. You must find and delete all existing duplicate records in that field before Access will allow you to set the 'Yes (No Duplicates)' property.

How can I find duplicates if I don't know which fields contain them?

You can use the built-in Find Duplicates Query Wizard. Go to the Create tab, click 'Query Wizard', select 'Find Duplicates Query Wizard', and follow the on-screen prompts to identify which tables and fields contain duplicate entries.

Office Quick Solutions