Create Fields on Report based on # of values in Query

[holgi123]

Hi everyone,

I would like to get some help on the following problem:

I have a report based on a query, which basically works fine. At the end I want to show number of sales packages by type, like this:

# of Package A: 10
# of Package B: 34
....
....
....

So far I do this by creating several field manually and than select the type of package by hard-coding it into the control source like this:
=Sum([OfferMade] ALike "Package A" And [SalesMade] ALike "Yes")
Next Box has than the code
=Sum([OfferMade] ALike "Package B" And [SalesMade] ALike "Yes")

It works but is time consuming as when the promotion code changes, I have to manually change the code.

I would like to get some help to dynamically create # of controls depending on the # of occurrences in the underlying query column (ideally together with a SUM() operation. So if the team only sold 12x packages C and 50x package D the report would show:

Packages C: 12
Packages D: 50

This way, each time the promocode changes I do not need to adjust the coding in any report. Is this possible and how would I do it?

Many thanks for anyone who helps.

 

[PHV]

You may consider a subreport based on an aggregate query:
SELECT OfferMade, Count(*) As CountOfSales
FROM yourQuery
WHERE SalesMade = "Yes"
GROUP BY OfferMade

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[holgi123]

Hi PHV,

Thank You. It is perfect, it works fine and gives me automatically the info which saves me time. I even got it working within my defined report groups.

Again many thanks for your help.

Regards