Others

How to Delete Duplicate Records Based on All Fields in Microsoft Access

Natalie TaylorNatalie Taylor Nov 2, 2026 868 views

Question details

The user needs a method to identify and delete duplicate records in a database table that match across more than 10 fields, while retaining one instance of each duplicate group.

How to Delete Duplicate Records Based on All Fields in Microsoft Access
Product
Microsoft Access
Device & OS
not provided
Scenario
Cleaning up a large dataset where exact duplicate rows exist across multiple fields, requiring targeted deletion of only the redundant copies.
Observed behavior
A standard DELETE query removes all instances of the matching records because it cannot distinguish between the exact duplicates, resulting in data loss.
Before you start

Always create a complete backup copy of your database or the specific table before executing any DELETE queries to prevent accidental data loss.

Solution 1Recommended

Use an AutoNumber Primary Key and a SQL DELETE Query

By adding an AutoNumber field, you assign a unique ID to every row. This allows Access to differentiate between exact duplicates and delete only the ones with the higher or lower AutoID.

To delete only one duplicate record while leaving the other, the database needs a way to distinguish between them. An AutoNumber primary key provides a unique identifier for each row, even if all the data in the other fields are identical.

Once the unique ID is established, you can group the records by your target fields and delete the rows that do not hold the minimum (or maximum) ID value in each group.

1
Add an AutoNumber Field

Open your target table in 'Design View'. Add a new field named 'AutoID' and set its Data Type to 'AutoNumber'. Save and close the table.

2
Open a Blank Query

Go to the 'Create' tab on the ribbon and click 'Query Design'. Close the 'Show Table' dialog box without selecting any tables.

3
Switch to SQL View

Click the 'View' drop-down arrow on the Design tab and select 'SQL View'.

4
Write the DELETE Statement

Type the SQL command to delete the duplicates, grouping by all the fields that must match. For example: DELETE FROM YourTable WHERE AutoID NOT IN (SELECT MIN(AutoID) FROM YourTable GROUP BY Field1, Field2, Field3...);

5
Run the Query

Click the red 'Run' exclamation mark icon on the Design tab. Confirm the prompt warning you that records will be deleted.

Use an AutoNumber Primary Key and a SQL DELETE Query
Handling Many Fields: If you have more than 10 fields, make sure you include every single field you want to match in the 'GROUP BY' clause of the SQL statement.
Free Microsoft Office alternative

Easily Manage Large Datasets and Remove Duplicates with WPS Office

While WPS Office does not include a direct relational database application like Microsoft Access, it is an exceptionally powerful, free alternative to Microsoft Word, Excel, and PowerPoint. If you are managing large data sets in tables, WPS Spreadsheet offers a lightweight interface with built-in tools to instantly highlight and remove duplicate rows without having to write complex SQL queries.

  1. 1. Open Your Data: Launch WPS Spreadsheet and open your dataset or import your CSV file.
  2. 2. Select the Data Range: Highlight the entire table or the specific columns where you want to check for duplicates.
  3. 3. Remove Duplicates: Go to the 'Data' tab on the ribbon and click 'Remove Duplicates'. Select the fields you want to match and click 'OK'.
100% compatible with Microsoft Excel (.xlsx, .xls), Word (.docx), and PowerPoint (.pptx) formats.Built-in 'Remove Duplicates' feature lets you clean matching records across multiple columns with just two clicks.Lightweight design ensures fast loading and smooth performance even when handling large spreadsheets.Free to use with a familiar, easy-to-navigate tabbed user interface, ensuring a seamless migration.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my DELETE query remove both duplicate records in Access?

Standard DELETE queries remove all records that match the specified criteria. Without a unique identifier like an AutoNumber field, Access cannot differentiate between the duplicate rows, resulting in all matching rows being deleted simultaneously.

Can I use the 'Find Duplicates' Query Wizard to delete records?

The Find Duplicates Query Wizard only helps you identify and view duplicate records as a Select Query. To actually remove them, you must convert the results into a DELETE query or use the query as a reference to manually delete the redundant rows.

How many fields can I compare when looking for duplicates in an Access database?

You can compare multiple fields in Access by including them in your SQL GROUP BY clause. While there is no strict limit that stops you at 10 fields, highly complex queries comparing numerous fields across very large tables may take significantly longer to process.