Select client records if columns don't match 2

[chuchuchui]

Hello, I work for an insurance company and I'm trying to select clients whose policies have more than one agent. All the information is located within our Policies table. The Policies.CLIENTS_ID column contains the numeric representation of our clients. The Policies.PROD1 contains the id for our agents. The Policies.CLIENTS_ID is listed each time there is a policy. First I'm assuming that I group by Policies.CLIENTS_ID but I'm not sure how to go about comparing the clients records to return those who Policies.PROD1 do not match. I'm running Crystal Reports 8.5 in Windows 2000. Thank You

 

[paulmarr]

I will assume if you group by CLIENTS_ID - this will give you multiple records for clients you have more than one agent.

Insert a running total that does a Distinct Count of PROD1 for each group.

Then use group selection (which is really group suppression) to only show groups where the running total >1.

Hope this helps

paulmarr

 

[lbass]

Group on {Policies.ClientID} and then go to report->edit selection formula->GROUP and enter:

distinctcount({Policies.Prod1},{Policies.ClientID}) > 1

As far as I know, you can't use running totals in group selection formulas. I also think of group selection as a subselect that "lifts" records out of the main selection pool. One difference between group selection and suppression methods: If you insert a running total on group selected records, it will only count the displayed records, not the underlying ones. If instead of using group selection you used suppression, if you inserted a running total it would count the suppressed as well as the displayed records.

-LB

 

[paulmarr]

Yes, lbass you are correct - I was thinking running totals because I know if you use Group Selection (which is really suppression - the records aren't gone - they are just hidden) and you want your totals to match to the visible records then summary totals won't do.

 

[lbass]

Yes, the records are still there, but I don't consider them suppressed either, since they don't behave like suppressed records do when using running totals, although they do behave the same when using conventional summaries. With either suppression or group selection, all records will be counted using conventional summaries.

Visually, I think of group selected records as lifted up into a higher level that running totals run across. Suppressed records are at "ground level" and get counted by running totals or summaries, unless you add conditions saying they should not be counted.

-LB

 

[paulmarr]

True - I guess I say suppression because the groups still appear in the group tree and using normal summaries - the grandtotal does not make up the total of the visible subtotals.

Cheers,

paulmarr

 

[lbass]

I agree.

Regards,
-LB

 

[chuchuchui]

Thank you both for your help, it works great.