select records with only a particular record

[chuchuchui]

Crystal XI, IBM Universe.

I'm trying to select client who only have a particular type of policy. Our {Policies.Coverages_ID} table contains this info and the policy type I'm trying to select is 53. What should show up is clients who only have 53 as their only policies. The client may have more than one instance of the policy type but cannot have any of the other policy types that exsist.

 

[dgillz]

Have you written a record selection formula yet? If so please share it and what did not work about it.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[synapsevampire]

Try:

Group by the client, and create 2 formulas:

//@not53
If {Policies.Coverages_ID} <> 53 then
1
else
0

Then create another of:

//@is53
If {Policies.Coverages_ID} = 53 then
1
else
0

Under Report->Selection Formulas Group place:

sum(@not53),{table.client) = 0
and
sum(@is53),{table.client) > 0

Better still you'd create SQL in a command object to do this using a NOT IN or a subquery or some such...

-k

 

[chuchuchui]

Thanks, that worked like a charm.