Marking Duplicate Records in a Database


The following is a method of marking and/or deleting duplicates in a Q&A  
database.  Also available from Q&A Technical Support is a utility disk called  
"Undupe".

This application Note should be used when one field in the database will 
determine two records to be duplicates.  For example if the address matches on 
two records you would want to mark one of the records.  The utility "Undupe"  
will let you match up to three fields to determine a duplicate.

Redesign the database and at the end of the form add two new fields:

                   REC #:
                   CHECK/MARK:

Make REC #: a numeric field (N) and Check/Mark: a text field (T).  Select
Speed up Searches from the Customize menu and put an S in the REC #: field.

From the File menu choose mass update.  On the retrieve spec press F8 for the
sort spec.  In the field that will determine a duplicate put, 1AS (use DS on 
second pass to mark first record found in first pass).  For this
application note we will call the field that we are checking for duplicates  
ADDRESS:.  After the sort spec is finished press F10 for the update spec.  On 
the update spec in the following fields type:

    ADDRESS: #1
    REC #: #2 = @number
    CHECK/MARK: #3: #3 = @xlookup(@filename, #2 - 1, "REC #", "ADDRESS");
                    if #3 = #1 then #3 = "DUPLICATE" else #3 =  ""

When the Mass Update is finished the records that are duplicates will have the 
word DUPLICATE in the CHECK: field.  You can use this field for any Retrieve 
Spec.  You may want to print these records to be sure they are ones you want 
to remove.  To remove all the records which have DUPLICATE in the Check/Mark  
field choose Remove from the File Menu and retrieve the duplicates.




















Q & A Application Note       Rev. 6/92           #2108     Page 1 of 1