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


  

Section Menu 

Are you a University or College bookstore and want to order multiple books?  Email us at alphapress@hotmail.com or call us at 518-250-3890.  We will be happy to talk to you.  

Are you a professor and you need a sample copy?  We will be happy to send you a pdf or printed copy!  Email us at alphapress@hotmail.com.

Are you outside the United States?  We can ship multiple books worldwide in most countries.

  

 

 

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: