Find/Change/Delete duplicates

[Nickman]

Nickman
Hi

Table design is:

TCustomer TProduct TReport
.Cust_ID .Cust_ID .Cust_ID
.Name
.Zip

I have to find duplicates in TCustomer. The first found Record is saved and the TProdut.Cust_ID/TReport.Cust_ID of the duplicates has to be changed to the "first found record". Then the duplicate is to be deleted.

- I cannot copy the Table (ca. 3GB, over 1 mio. records)
- Using SQL Server 7.0 or Access2000, or ASP

What I've already got (Access):

SELECT 
 TCustomer.Name, 
 TCustomer.Zip, 
 First(TCustomer.Cust_ID) AS [First ID], 
 Count(TCustomer.Name) AS [Nr.of.Names], 
 Count(TCustomer.Zip) AS [Nr.of.Zips]
FROM 
 TCustomer
GROUP BY 
 TCustomer.Name, 
 TCustomer.Zip
HAVING 
 (((Count(TCustomer.Name))>1) AND ((Count(TCustomer.Zip))>1));

The result is:

Name Zip First ID Nr.of.Names Nr.of.Zip
Joe 88342 12345 4 4

Now I need to get the 2nd,3rd,4th,etc ID's so i can change TProduct & TReport and then delete them from TCustomer.

thx in advance
Nick

 

[tlbroadbent]

I assume you have saved the Access query that identifies the duplicates. You can use that query in another query to identify the duplicates.

SELECT
TCustomer.Name,
TCustomer.Zip,
TCustomer.Cust_ID
FROM TCustomer As a INNER JOIN qryDuplicatCustomers As b
ON a.Name=b.Name and a.Zip=b.Zip
WHERE a.Cust_ID<>b.Cust_ID Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[Nickman]

Thx terry

I was stuck on the innerjoint.