Group when two fields are the same

[BasilFawlty001]

I have one table. Field A contains contact name. Field B contains a number. Field C contains a number. I would like to group the names that have the same number in B OR C.

Example:
Row 1: Billy Bob has the number 123 in field B. C is blank
Row 2: Betty Sue has the number 123 in field C. B is blank

The report should show:
Billy Bob Betty Sue 123

Thanks in advance!

 

[dgillz]

What would happen if both B and C were populated with different numbers? Could that ever happen? Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[BasilFawlty001]

In theory that should never happen. I know what you're thinking ... why not just use one field? Sufice it to say that we can't at this point, for future considerations. having said that, it might be that in the future that both are populated in a certain row so a solution with that in mind would be ideal. Thanks.

 

[dgillz]

Ok, so only one of the fields is populated? Write a formula:

If Isnull({field1}) then {field2} else {field1}

Then group by the formula field. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[BasilFawlty001]

Perfect. I had to add field1 = "" (not all were Null) and it worked great. Thanks.

 

[BasilFawlty001]

Ok, step 2. What would be the best approach to put Billy Bob first in the group? For each entry in Field B (123 in this case) there may be many in Field C. IE: Field B is unique while C is not. I've got Billy Bob in bold right now and Betty Sue in regular text but I'd like to ensure that Billy Bob is always first.

 

[dgillz]

I need to back up a bit. You said the report should look like this:
Billy Bob Betty Sue 123

why not like this:
Bill Bob 123
Betty Sue 123

Please elaborate



Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[BasilFawlty001]

The second is fine too. I've now just sorted by Field B and it does what I need (because the others are all blank). Thanks for your help, you saved me many hours of work!

 

[dgillz]

The bill in is the mail!

Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com