Conditional GroupBy

  • Thread starter Thread starter Razor
  • Start date Start date
R

Razor

Hi,

I want to create a report whose group by clause depends on
the value in a field.

I have 10 distinct products (known as "A" through "J") and
8 subproducts under each product.

If the product is A or B, I want to group by Product and
SubProduct
Else (for one of the other 8)
I want to group by Product only.

How would I specify this in my report?

Do I need to work this info into the recordsource or can I
do this at the report level (Sorting and Grouping window)?

Thanks,

Razor
 
Razor,

You can do either. Probably easiest in the report's Sorting &
Grouping dialog. In the Field/Expression you can type something
like...
=IIf([Product] In("A","B"),[SubProduct],[Product])
.... and then in the controlsource of a textbox in this group header,
you can put whatever you want the heading to be, for example...
=[Product] & IIf([Product] In("A","B"),": " & [SubProduct])

- Steve Schapel, Microsoft Access MVP
 
Back
Top