Union Crosstab queries (1 Viewer)

[niloufar68]

Hi;
I'm trying to union two cross-tab queris. I get a syntax error each time I try to run the code. The code is the following:

TRANSFORM Count(Table1.Q1) AS CountOfQ1
SELECT Table1.Date
FROM Table1
GROUP BY Table1.Date
PIVOT Table1.Q2
UNION
TRANSFORM Count(Table1.Q2) AS CountOfQ2
SELECT Table1.Date
FROM Table1
GROUP BY Table1.Date
PIVOT Table1.Q1;
I get this error:
Syntax error, missing operator in query expression 'Table1.Q2'
Can anyone please help me with this? is it even possible to combine two crosstab queries?


Thank you,

 

[Guus2005]

No, it is not possible to combine two (or more) crosstab queries.
Create a union query and after that you can create a crosstab query.
Creating and displaying the results of a crosstab query is typically a thing you do last.

If the table is the same and Q1 and Q2 are fields in that table you might want to consider normalising your table. Perhaps this solves the problem you are experiencing.

HTH

 

[sxschech]

You could try it like this, which is essentially what Guus2005 said in that you would union and then crosstab. This approach allows you to do it all with one query. In my test needed to add an additional field which I called DateRange. It may need further modification to work in your situation, but gives you the idea.

[COLOR=blue]TRANSFORM Count(Q1) AS CountofQ1
SELECT "Group" AS DateRange
FROM (SELECT table1.Date Q1
FROM Table1
UNION
SELECT table1.Date
FROM Table1)  
GROUP BY "Group"
PIVOT Q1;
[/COLOR]