Select Distinct Records - based on multiple fields

[IbeKaba]

I'm working in Crystal 9 on a database I'm not too familiar with. The primary key seems to be a combination of multiple fields. Hence I have records that are almost identical with only slighth variation in a field or two. Or maybe there is no primary key.
Anyway, I'm not sure how to explain my problem, so I'll illustrate it.

sample data:
ID TAG
123 red
123 blue
132 red
122 blue

I want to select all ID's that have "red" and only "red" TAG
In this case, I want only ID 132.
When I do a simple select to give me all records where the TAG is "red", it gives me 123 and 132. Which I can sort of understand. However, my issue here is, since there is another record with ID 123 with a "blue" TAG, I don't want to select it. How do I go about doing this?

Hope that makes sense
Thanks in advance
ibe

 

[synapsevampire]

Group by the ID field.

Create a formula:

//@CountRed
if {table.tag} = "red" then
1
else
0

Go to the Report->Selection Formulas->Group and place:

sum(@CountRed,{table.ID}) > 0
and
count({table.tag},{table.ID}) = sum(@CountRed,{table.ID})

This will return those IDs that have ONLY red as the TAG.

-k

 

[IbeKaba]

Thanks a lot. It works
Though I must say, I don't get it
Maybe I don't remember much of the programing I learned back in college, but looking at the formular, I thought it would assign "1" to even those ID's that have other color TAGs as long as they have "red". Maybe the selection formular is where the separation comes. I'm learning.
Anyway, I appreciate the help
ibe

 

[synapsevampire]

the formula says to provide a 1 for ONLY those with a 1, and the group summary makes sure that the REDS are the only ones by comparing the red count to the count of all the rows for that group.

-k