Almost duplicates?

[GMAN33]

Crystal Reports XI, Oracle 10g

We collect and scan documents for patients into our DB. When a patient receives a record, it is marked as being an original "Y" or not an original "N" copy of a form. However, they could receive the same form over and over again until the original is received. This information is stored in one table {DOCUMENTS}

So I can have the following

SITE SUBJECT PAGE NO ORIGINAL?
001 2010 1 N
001 2010 1 Y
001 2010 1 N
003 0900 12 Y
003 0900 12 N
002 1200 201.1 Y
002 1200 201.1 N
002 1200 201.1 N

I want to show just the subjects that have an N and not both an N and a Y for each site/subject. Hope I explained this ok and provided enough info.

Thank you!!

 

[pmax9999]

Create a Group on {Documents.Subject}.

If the Maximum({Documents.Original}) = 'N' and the Minimum({Documents.Original}) = 'N' for all records in the group then they are all copies. You could use this in the Group Selection formula as follows:

[Code Group_Selection_Formula]
Maximum({Documents.Original},{Documents.Subject}) = 'N' and
Minimum({Documents.Original},{Documents.Subject}) = 'N'
[/Code]

Cheers
Pete

 

[CoSpringsGuy]

DistinctCount({Documents.Original},{Documents.Subject}) > 1

would work as well

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[pmax9999]

CoSpringsGuy, I had thought of that approach but discounted it because it would also return records that were only "Y" as well. Not sure if it is actually possible, maybe not.

Regards
Pete

 

[CoSpringsGuy]

ouch! You are right Pete.. Where is the "Delete the post you didn't think all the way through" button

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[pmax9999]

Dunno, but there have been quite a few occasions where I needed it too. Let me know if you find it.

 

[GMAN33]

Thank you both very much!! This appears to have worked!! Have a great one!!

 

[CoSpringsGuy]

i didn't do anything but offer an incorrect solution Be sure and "like" pmax solution so others can see this has been resolved

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection