Show ONLY Duplicate + Original Records

[MollyH]

Here's my dilemna - I need to see the duplicate records for a person - both the original AND the duplicate. This person may have multiple records, but only 1 is duplicated, and those are the only ones I want to see to determine if it is a true duplicate and remove the record from the database.

I'm using CR8.5 and an Informix database.

The records are grouped by ID#(record.partid), with fields record.recordid, record.merchantid, record.checkdate, record.checknumber, and record.checkamount. The fields that must match to be considered a dulicate are partid +checknumber+checkdate+checkamount+merchantid. Each would be assigned a unique recordid upon creation of that record, but they must match all criteria to be considered a true duplicate.

Currently, it looks like this:

145789
RCID1 MCID1 05/01/07 5545 $55.69
RCID2 MCID2 05/02/07 5546 $100.50
RCID3 MCID2 05/02/07 5546 $100.50
RCID4 MCID3 05/05/07 5592 $75.00

When I try to suppress all that are not duplicates, I get this (using next or previous):

145789
RCID3 MCID2 05/02/07 5546 $100.50

I would like to see:

145789
RCID2 MCID2 05/02/07 5546 $100.50
RCID3 MCID2 05/02/07 5546 $100.50

All help to this long (I know, sorry) request is greatly appreciated!!

 

[Turkbear]

Hi,
How can these be duplicates:

RCID2 MCID2 05/02/07 5546 $100.50
RCID3 MCID2 05/02/07 5546 $100.50

RCID2 is not the same as RCID3

What method are you trying for:

When I try to suppress all that are not duplicates

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[MollyH]

They are duplicates because under the same partid grouping (145789), the merchant id, check date, check number and check amount all match. The RCID is assigned at the time the record is created, like a sequential row number or reference number.

 

[RustyAfro]

As turkbear asked above, we need to know what you mean by:

"When I try to suppress all that are not duplicates"

How are you trying to suppress them? If using a formula, please copy/paste the formula.

 

[synapsevampire]

Whatever fields are duplicates need to be used in the formula.

You speak of using certain fields, and then in your example data you don't show which fields are which, you need to take some time and think about what your posting, you'll only confuse people...

So here's some theory towards your solution:

Create a formula which concatenates all of the fields involved in determining a dupe such as:

{table.field1} & {table.field2} & {table.field3} ...etc...

Group the report on this field.

Now in the Report->Selection Formula->Group place:

count({@mygroupfield},{@mygroupfield}) > 1

-k