Last updated: August 28, 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.
Reference: Microsoft guide to finding duplicate records.
