Crosstab dual columns?

[Jen]

Is there anyway to make a crosstab query/report have 2 column headings.

I need my end result to look something like below (where Month & C170/RC85
are column headings & RvwRsn for row data):
(column) (column)
(row) Jan Feb Mar
Apr........ Total
RvwRsn C170 RC85 C170 RC85 C170 RC85 C170 RC85 C170 RC85

BDEN 4 2 10 1 15 2 0
9 29 14
BNDT 0 9 1 10 8 7 6
3 29 29
D999 1 13 0 9 8 7 6
3 15 32
W0353 4 8 6 9 3 6 2
8 15 31

Thanks!
Jen

 

[Michel Walsh]

The easiest way would be to create two crosstabs and to join them, in a
third query, on the field(s) that were used to defined the groups.

As usual, you can use a form (with controls) to dispose the fields the way
which is the more convenient to you.


Vanderghast, Access MVP

 

[Jen]

In a third crosstab or select query?

The easiest way would be to create two crosstabs and to join them, in a
third query, on the field(s) that were used to defined the groups.

As usual, you can use a form (with controls) to dispose the fields the way
which is the more convenient to you.


Vanderghast, Access MVP

 

[Michel Walsh]

The last query would be a select query.

One of the crosstab will filter for C170 values, pivot by month, group by
RvwRsn
Another of the crosstab will filter for RC85, pivot by month, group by
RvwRsn
The last select query join Xtab1 with Xtab2 on RvwRsn:

SELECT *
FROM Xtab1 INNER JOIN Xtab2 ON Xtab1.RvwRsn = Xtab2.RvwRsn



Note that the columns name, from SQL, will be Xtab1.Jan and Xtab2.Jan;
but, sure, Xtab1 would be about C170 and Xtab2 would be about RC85.




Vanderghast, Access MVP

 

[Jen]

Thanks Michel.
This isn't going to work as there could be a rvw rsn in C170 that is not in
RC85. I need to capture ALL rvw rsn's.

 

[Michel Walsh]

Ah, then you need a full outer join, not supported directly in Jet, but easy
to simulate it if you have a table with all the possible RvwRsn value (no
dup):


SELECT *
FROM ( AllRvwRsn LEFT JOIN XTab1 AS C170 ON AllRvwRsn.RvwRns=C170.RvwRsn)
LEFT JOIN XTab2 AS RC85 ON AllRvwRsn.RvwRsn=RC85.RvwRsn


Vanderghast, Access MVP

 

[Jen]

Thanks!

Ah, then you need a full outer join, not supported directly in Jet, but easy
to simulate it if you have a table with all the possible RvwRsn value (no
dup):


SELECT *
FROM ( AllRvwRsn LEFT JOIN XTab1 AS C170 ON AllRvwRsn.RvwRns=C170.RvwRsn)
LEFT JOIN XTab2 AS RC85 ON AllRvwRsn.RvwRsn=RC85.RvwRsn


Vanderghast, Access MVP