Counting multiple instances? 1

[uczmeg]

Hi,

I have a set of product data. For each customer, each product has a discount and a category. I need to select distinct the catgory and the discounts and order the data in the following way (discount ascending).

Like this:

category discount
cat 1 5%
cat 2 6%
cat 3 7%
cat 1 10%
cat 4 10%

I have no problem selecting and reporting on the data, but I need a third column of info to say that the category has multiple discounts (cat 1 in the example appears twice meaning it has products in it with two different discounts)

eg:

category discount multiple
cat 1 5% yes
cat 2 6%
cat 3 7%
cat 1 10% yes
cat 4 10%

Problem is I haven't a clue how to achieve this! Can anyone help?

Thanks
Marc

 

[uczmeg]

Sorry, should add I'm using Crystal 8.5 on to an Access database.

 

[lbass]

One way to handle this would be to insert a subreport which is linked to the main report only on category. In the subreport, add the category field and the discount field, and then add a formula:

if distinctcount({table.discount}) > 1 then "Yes"

Place this in the subreport footer and suppress all other sections.

If the discount is a database field, not a formula, the other approach you might take is to use a SQL expression {%disccnt}, something like:

(Select count(A.`discount`) from Table A
where A.`category` = Table.`category`)

Then in the main report, you could use a formula like:

if {%disccnt} > 1 then "Yes"

-LB

 

[uczmeg]

Thanks LB.

Not sure option 1 can work, as just passing the category is not enough. I'd need to pass the customer too.

Option 2 sounds plausible, although it could mean a hell of a lot of queries if they were running for all customers!

I've never used SQL expressions and I'm not sure about the code you are using as an example. How do I reference the category that I am on. Or is table.'category' referencing the current position.

The discount is a database field and the list of categories is pulled back by this crystal generated sql:

SELECT DISTINCT
customer_sets.`customer_code`,
price_sets_data.`modifier1`,
category.`descr`
FROM
((`customer_sets` customer_sets INNER JOIN `price_sets_data` price_sets_data ON
customer_sets.`set_code` = price_sets_data.`set_code`)
INNER JOIN `master_list` master_list ON
price_sets_data.`code` = master_list.`code`)
INNER JOIN `category` category ON
master_list.`category` = category.`category_id`
WHERE
customer_sets.`customer_code` = 'A1D001'

ORDER BY
price_sets_data.`modifier1` ASC,
category.`descr` ASC


Where modifier1 is the discount.

Can you be any more help?

 

[lbass]

With multiple tables, I think option 1 is the best. Just link on customer and category then.

-LB

 

[uczmeg]

Thanks, sorted that now.