How to create crosstab query or equivalent with multiple row headi

[Guest]

Hello All,

I have a table with following text fields: Area, Category, Reason

I'd like a query to group by Category, then by Reason and sum the occurrence
of the reason by Area as follow:

Health Accident Vacation
R1 R2 R3 R4 R5 R6 R7
Area1 3 0 1 0 0 0 0
Area2 0 0 0 0 2 0 2
Area3 2 4 0 5 0 8 2
Area4 1 0 0 0 0 0 3
Area5 0 0 3 2 6 3 4
Area6 3 2 0 0 0 0 2
Area7 2 0 0 0 3 0 5

I have been able to get one row heading and value with a crosstab query.
It seems to me I need the ability to have two row headings.
Any help is appreciated.

Thank you.

 

[Wolfgang Kais]

Hello ManOnPlanet.

Hello All,

I have a table with following text fields: Area, Category, Reason

I'd like a query to group by Category, then by Reason and sum the
occurrence of the reason by Area as follow:

Health Accident Vacation
R1 R2 R3 R4 R5 R6 R7
Area1 3 0 1 0 0 0 0
Area2 0 0 0 0 2 0 2
Area3 2 4 0 5 0 8 2
Area4 1 0 0 0 0 0 3
Area5 0 0 3 2 6 3 4
Area6 3 2 0 0 0 0 2
Area7 2 0 0 0 3 0 5

I have been able to get one row heading and value with a crosstab
query. It seems to me I need the ability to have two row headings.
Any help is appreciated.

What you want is 2 column headings, and that's impossible. But you can
have 2 row headings indeed: Category and Reason. Use Area as column
heading. If you need two column headings, use a PivotTable instead.