Last updated: August 29, 2026.
A duplicate query first defines what makes two records equivalent. An email address may identify a customer, while an invoice may require the same customer, date, and amount. Keep the primary key out of the grouping because it is intentionally unique.
Find duplicated values
Group by the business key and retain groups whose count is greater than one.
SELECT EmailAddress, Count(*) AS DuplicateCount
FROM Customers
WHERE EmailAddress Is Not Null
GROUP BY EmailAddress
HAVING Count(*) > 1
ORDER BY Count(*) DESC, EmailAddress;Return the complete records
Join the grouped result back to the source table. Save the first query as qryDuplicateEmails, then use it here.
SELECT c.CustomerID, c.CompanyName, c.EmailAddress
FROM Customers AS c
INNER JOIN qryDuplicateEmails AS d
ON c.EmailAddress = d.EmailAddress
ORDER BY c.EmailAddress, c.CustomerID;Normalize only when the rule requires it
If case and surrounding spaces should be ignored, group by LCase(Trim([EmailAddress])). Do not remove punctuation or merge names automatically without a reviewed business rule. Inspect duplicates before running any delete query, and keep the record that owns related data.
Define what duplicate means
Choose a business key before grouping. An email address may identify a customer, while a transaction may require the same account, date, and amount. Normalize only the differences the business considers irrelevant.
Review the complete matching rows before deleting or merging anything. Confirm which record owns related data and keep a backup or transaction boundary for corrective work.
- Exclude the primary key from grouping.
- Handle Null values deliberately.
- Add indexes to frequently matched fields.
Use realistic sample data that includes Nulls, ties, and boundary dates. Save a copy before converting a SELECT query into an action query, and compare row counts before relying on the result.
Continue with top records per group and Null handling.
Practical implementation check
Build and save the SELECT version before using the result in a form, report, export, or action query. Test records containing Nulls, duplicate sort values, missing dates, and boundary dates. For larger tables, compare execution with appropriate indexes and avoid wrapping an indexed field in a formatting function when ordinary range criteria can do the same work.
Reference: Microsoft guide to finding duplicate records.
