How to Identify Duplicate values 1

[ziggs]

I have a database with one field that I want to look for duplicate values. We'll call it field1. So, if field1 has 500 rows in it and five of those rows have dupicate values, I just want my report to show those rows (original value and duplicate value). Thus, I should see 10 rows in my report (5 original and the 5 duplicates. Also, if there were triple entries, etc, the report would reflect according.

Any ideas? TIA

 

[MrBillSC]

Hi Ziggs,

To find duplicate values in a table you will have to link the table to itself. Simply select your table 2 times and Crystal will give the second table an alias. Next you want to link the table by the fields you want to find dups on (in your example it is field1).
So now we have TableA linking to TableA_1 using field1 as the linking field.

Create a formula field called PrimRec that uses the Nth largest function to find the largest Key value among the records with the duplicate values:
NthLargest(1,TableA.UniqueKey,field1)
We will use this formula field to always select the record with the highest value in its unique key field to be the TableA record that links to the other "duplicate" records in TableA_1.

In the select expert window select if:
{@PrimRec} = {TableA.UniqueKey} and
{TableA.UniqueKey} <> {TableA_1.UniqueKey}

This keeps the record in TableA from linking to itself in TableA_1.

 

[MrBillSC]

Oops,

the Nth largest function cannot be used during selection, so use the following code in your formula PrimRec:

If{TableA.UniqueKey} > {TableA_1.UniqueKey} then
{TableA.UniqueKey} else
{TableA_1.UniqueKey}

Sorry about that.

MrBill