Prev: Multi-field query calculations
Next: Extracting a word from a long text field
From: cinnie on 10 Apr 2010 22:08
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
From: Duane Hookom on 11 Apr 2010 00:56
There is a solution for multiple value crosstabs at
http://www.tek-tips.com/faqs.cfm?fid=4524.

--
Duane Hookom
MS Access MVP


"cinnie" <cinnie(a)discussions.microsoft.com> wrote in message
news:534F5C06-506C-4655-9F98-345A91308A9E(a)microsoft.com...
> 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

 | 
Pages: 1
Prev: Multi-field query calculations
Next: Extracting a word from a long text field