comparing client records and selecting ones with certain criteria 1

[chuchuchui]

As I work for an insurance company I am trying to pull all our clients that only have one type of policy ie only have an auto policy or only have a home policy but do not have both type of policies. The column Cient_ID contains the numeric representation of evey client in our system. Coverage_ID contains the numeric version of our coverages. The following numbers are our auto coverage id's: 3, 14, 80, 82, 73 and the following are the numeric representation of our homes: 57, 61, 73, 83, 87. 73 is a type of policy that includes both home and auto. This is all in one table. I'm fairly new to this and need some help/direction on this. Also, are there any beginner books that people have found really helpful.

 

[chuchuchui]

oh, I almost forgot. I'm using Crystal Reports version 8.5, the database is IBM Universe on a Windows 2000 server.

 

[lbass]

First group on {table.client_ID}. Then create a formula {@type}:

if {table.coverage_id} in [3, 14, 80, 82] then 1 else
if {table.coverage_id} in [57, 61, 83, 87] then 1000 else
if {table.coverage_id} = 73 then 1000000

Then go to report->edit selection formula->GROUP and enter:

sum({@type},{table.client_ID}) < 1000 or
(
sum({@type},{table.client_ID}) in 1000 to 999999 and
remainder(sum({@type},{table.client_id}),1000) = 0
)

The George Peck books "The Complete Reference: (Seagate) Crystal Reports 8", etc. are good. Not sure what version number he is up to now. The Help files are good for learning about functions, and reading posts in Tek-Tips will give you the extra tips/tricks not always found in books. Also check out Ken Hamady's website.

-LB