To delete duplicates safely in Access, define what makes a row duplicate, choose which primary key to keep, preview the rows that will be removed, and only then run the delete query. Never use every business column as the deletion target without a stable unique ID.
Last updated: September 26, 2026.
DELETE Contacts.*
FROM Contacts
WHERE Contacts.ContactID NOT IN (
SELECT Min(Keepers.ContactID)
FROM Contacts AS Keepers
WHERE Keepers.Email Is Not Null
GROUP BY LCase(Trim(Keepers.Email))
);This example treats normalized email as the duplicate key and keeps the smallest ContactID. Rows with a Null email are excluded so the NOT IN comparison is not poisoned by Null.
Preview the deletion first
Change DELETE Contacts.* to SELECT Contacts.* and run the query. Compare the result with the companion guide for finding duplicate records. If capitalization or spaces matter to your data, remove LCase or Trim rather than normalizing automatically.
Save a copy of the table before deletion. If related tables reference ContactID, decide whether those rows must be reassigned to the retained contact. Referential integrity may block the delete, while cascade delete can remove more data than intended.
Choose the keeper explicitly
Min(ContactID) keeps the earliest AutoNumber, but that may not be the best row. To keep the most recently modified record, first create a query that returns the desired keeper ID per duplicate group. Review ties: two rows can share the same timestamp, so add the primary key as a deterministic tie-breaker.
- Duplicate key: the normalized fields that identify the same entity.
- Keeper rule: lowest ID, newest timestamp, or most complete row.
- Merge rule: how related records and nonblank values are preserved.
Avoid repeating the problem
After cleanup, add a unique index when the business rule truly requires uniqueness. If email can be Null or can change, a separate normalized key may be more appropriate. Clean incoming data before an update query joins it into the main table.
