Browsing a Table for Duplicate Records

[trick66]

Ok, I've search for tips on duplicate records, but none actually tells me how to browse the duplicate records.

I would like to be able to browse a single table for duplicate records either based on a few fields or every fields.

The reason I want to browse is because I would like to be able to manually tag whichever records that I want to remove.

Any advice is appreciated. Thanks

Rick

 

[torturedmind]

there are some good suggestions already posted a while back. try searching for them using "duplicate records" as your search string and it will return fruitful results.

hope this helps. peace!

kilroy
philippines

"Illegitimis non carborundum!"

 

[trick66]

Thanks for the suggestions Kilroy, but I tried the search before I even posted. None of the tips showed me how to BROWSE the duplicate records. Maybe I'm missing something.

Rick

 

[torturedmind]

here's one of them: thread184-851515

hope this helps. peace!

kilroy
philippines

"Illegitimis non carborundum!"

 

[Mike Lewis]

Rick,

Do you want to see each instance of the duplicated records, or is it enough to just see the duplicated field?

If the latter, the following code would do the trick. Suppose you have a name and address table, and you just want to know which names occur more than once:

SELECT Name FROM MyTable GROUP BY Name HAVING Count(Name)> 1

If you want to find records where multiple fields are identical, you could extend the above idea, but it becomes a bit clumsy:

SELECT Name + Address + City + Country AS X FROM MyTable;
GROUP BY X;
HAVING COUNT(Name+Address+City+Country) > 1

If you want to see all fields in a record where only one field is duplicated, you will have to do something like this:

SELECT * FROM MyTable WHERE Name IN ;
(SELECT Name FROM MyTable GROUP BY Name HAVING Count(Name)> 1)

I haven't tested the above code, but have used similar techniques myself many times. If the above doesn't meet your needs, perhaps you can give more details of what you want to do.

Mike



Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[Stella740pl]

It appears that trick66 wants to browse all the fields of the duplicate records right in the original table, to manualy delete some of the instances based on the non-duplicated fields and keep others.

In this case, I would go with modified Mike Lewis's solution:

SELECT * FROM MyTable ;
   WHERE Name IN ;
     (SELECT Name ;
      FROM MyTable ;
      GROUP BY Name ;
      HAVING Count(Name)>1) ;
      INTO ARRAY Duplicates

IF _TALLY>0
   SELECT MyTable
   BROWSE FOR ASCAN(Duplicates, Name)>0
ENDIF

Stella