Is this but not that

[Paul1981]

Hi,

I'm using Crystal Reports XI and have a problem which I hope someone can help with.

I am running a query using over 3 table (customer, product and sale).

If I run a report over Sale I can find all the products a customer has bought, ie:

customer001 product001
customer001 product002
customer001 product003
customer001 product001
customer002 product001

The question then:

How do I say "give me every customer which has puchased Produce001 OR Product002 BUT NOT Product003"?

In query, I would run two seperate queries. One to find everyone who has Product001 OR Product002. And then I would run a second query to find all which have not purchased product003. And then match on the customer number. - but how do I do this in Crystal??

Hope you can help.

Paul

 

[IanWaterman]

In you select statement

product <> product003

Ian

 

[lbass]

Use a record selection formula:

{table.product} in ["Product001","Product002","Product003"]

Insert a group on customer and then create a formula {@Prod3}:

if {table.product} = "Product003" then 1

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

sum({@Prod3},{table.customer}) = 0

Only people who have Products 1 or 2 or both and NOT Product 3 will be displayed on the report. If you want to do summaries across clients, you would need to use running totals, since non-group selected records contribute to the more usual inserted summaries.

-LB