CrossTab Query question

hello to all

I have a Report that is based on two crosstab queries, qryA and qryB (shown
below). Because these both have exactly the same Row Headings and exactly
corresponding records. They differ only in the PivotField Column Headings,
I'm wondering if they could be combined into a single query. (What I'm doing
now is combining qryA and qryB into a third qryAB, then basing my Report on
that, but I'd really like to know if this can be done in just one query).
I've tried many times but had no luck so far.

'qryA
TRANSFORM Count(A) AS CountOfA
SELECT qryEmp.ZoneID, qryEmp.Div
FROM qryEmp
GROUP BY qryEmp.ZoneID, qryEmp.Div
PIVOT qryEmp.A;

'qryB
TRANSFORM Count(B) AS CountOfB
SELECT qryEmp.ZoneID, qryEmp.Div
FROM qryEmp
GROUP BY qryEmp.ZoneID, qryEmp.Div
PIVOT qryEmp.B;

'qryAB
SELECT qryA.*, qryB.*
FROM qryA INNER JOIN qryB ON (qryA.ZoneID = qryB.ZoneID) AND (qryA.Div =
qryB.Div);

Thanks in advance!
--
cinnie

There is a solution for multiple value crosstabs at
http://www.tek-tips.com/faqs.cfm?fid=4524.

--
Duane Hookom
MS Access MVP


"cinnie" wrote in message
...
hello to all

I have a Report that is based on two crosstab queries, qryA and qryB
(shown
below). Because these both have exactly the same Row Headings and exactly
corresponding records. They differ only in the PivotField Column
Headings,
I'm wondering if they could be combined into a single query. (What I'm
doing
now is combining qryA and qryB into a third qryAB, then basing my Report
on
that, but I'd really like to know if this can be done in just one query).
I've tried many times but had no luck so far.

'qryA
TRANSFORM Count(A) AS CountOfA
SELECT qryEmp.ZoneID, qryEmp.Div
FROM qryEmp
GROUP BY qryEmp.ZoneID, qryEmp.Div
PIVOT qryEmp.A;

'qryB
TRANSFORM Count(B) AS CountOfB
SELECT qryEmp.ZoneID, qryEmp.Div
FROM qryEmp
GROUP BY qryEmp.ZoneID, qryEmp.Div
PIVOT qryEmp.B;

'qryAB
SELECT qryA.*, qryB.*
FROM qryA INNER JOIN qryB ON (qryA.ZoneID = qryB.ZoneID) AND (qryA.Div =
qryB.Div);

Thanks in advance!
--
cinnie