FILTER 'AND' TO MEAN RECORDS IN BOTH COMBINED FIELDS

[lulu801]

Hello - Crystal XI
Am trying to filter to include data that is in multiple "Districts". Currently, if I want data in Districts A and C, I will get all districts in A and all districts in C. I want only the districts in both A and C. These districts are in one table, so there is no option to link tables differently. I've tried creating formulas such that if in district a '1' else '0' and if in district c '1' else '0' in various combinations with no results.
Account 123 in district A
Account 124 in districts B and C
Account 125 in district C
Account 126 in district B
Account 127 in districts A and C
I want only account 127 returned.

I'm not that experienced, and have searched everywhere I can think of for some help. Can someone educate me?

 

[kskid]

Insert the table again as an alias, {table_1).

Record Selection would be

{table.account} = {table_1.account} and
{table.district} = "A" and
{table_1.district} = "B"


Or you could write a command

Select table.account,
table.district
from table
where table.district = "A" and
exists (select * from table tablex
where tablex.district = "B" and
tablex.account = table.account)

 

[lbass]

You started off correctly. Create two conditional formulas:

//{@A}:
if {table.district} = "A" then 1

//{@C}:
if {table.district} = "C" then 1

Insert a group on {table.account} and then go to report->selection formula->GROUP and enter:

sum({@A},{table.account}) > 0 and
sum({@C},{table.account}) > 0

If you then need to do any summaries across accounts, you will need to use running totals, since non-group-selected records will contribute to the more usual inserted summaries.

-LB