AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com  


  
Click to buy this book from Amazon

Section Menu 

News

Pindaro is now working on a comprehensive book on SQL Server 2014.  For any comments you can email the author at alphapress@hotmail.com.

  

 

 

Find duplicate records in a table based on the values of multiple fields

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


 

Find duplicate records in a table based on the values of multiple fields

Find duplicate customer orders based on the values of multiple fields

Discussion:

In this example, we are looking for duplicate records based on the values of eight fields.  In other words, if the values of eight fields of at least two records are identical, those records will appear as duplicates.  Of course, with eight identical values, two records will most probably be duplicate entries.  Notice that there are two identical records for each OrderID with values 8, 45, 254, and 820, while there are three identical records with OrderID = 993. 

Code:

SELECT Count(*) AS NumberofDuplicates, OrderID, CustomerID, SalesRepID, ShipperID, OrderDate, RequiredDate, ShippedDate, ShippingCost

 

FROM tbls_Orders

 

GROUP BY OrderID, CustomerID, SalesRepID, ShipperID, OrderDate, RequiredDate, ShippedDate, ShippingCost

HAVING count(*)>1

 

Result: