Crosstab Union query

[ragtopcaddy via AccessMonster.com]

I've read on this group that it's possible to create a Crosstab/Union query.
However, this doesn't work:

TRANSFORM First(yrSales)
SELECT "Sales" as Graph
FROM qryMktTtls
GROUP BY [Market#], "Sales"
PIVOT Year

Union

TRANSFORM First(yrProfit)
SELECT "Profits" as Graph
FROM qryMktTtls
GROUP BY [Market#], "Profits"
PIVOT Year

What do I have to do to make this work?

 

[Bob Barrows [MVP]]

I've read on this group that it's possible to create a Crosstab/Union
query. However, this doesn't work:

TRANSFORM First(yrSales)
SELECT "Sales" as Graph
FROM qryMktTtls
GROUP BY [Market#], "Sales"
PIVOT Year

Union

TRANSFORM First(yrProfit)
SELECT "Profits" as Graph
FROM qryMktTtls
GROUP BY [Market#], "Profits"
PIVOT Year

What do I have to do to make this work?

What does "doesn't work" mean? Error message? Machine crash? Incorrect
results?

Select statements in a union query must be guaranteed to return the same
number or columns of similar datatypes. I'm not going to try it, but if
you are getting an error message, I suspect Access might be balking
because the number of columns returned by each pivot is dynamic and not
guaranteed.

I suspect you would need to encapsulate each pivot in a query that
returns a predictable number of columns:

select Graph, [2007],[2008] From (
sales pivot query) as q
union
select Graph, [2007],[2008] From (
profit pivot query) as q

 

[ragtopcaddy via AccessMonster.com]

Both crosstab queries produce 4 columns of data, 2005, 2006, 2007, YTD.

When I try to run it, I get the error msg:

"Syntax error (missing operator) in query expression 'Year

Union

TRANSFORM First(yrProfit)
SELECT "Profits" as Graph
FROM qryMktTtls
GROUP BY [Market#], "Profits"
PIVOT Year'

I tried enclosing Year in square brackets ([Year]), thinking that maybe
Access mistook Year for a function, but that made no difference, except now
there were square brackets in the error msg.

I've read on this group that it's possible to create a Crosstab/Union
query. However, this doesn't work:

[quoted text clipped - 14 lines]

What do I have to do to make this work?

What does "doesn't work" mean? Error message? Machine crash? Incorrect
results?

Select statements in a union query must be guaranteed to return the same
number or columns of similar datatypes. I'm not going to try it, but if
you are getting an error message, I suspect Access might be balking
because the number of columns returned by each pivot is dynamic and not
guaranteed.

I suspect you would need to encapsulate each pivot in a query that
returns a predictable number of columns:

select Graph, [2007],[2008] From (
sales pivot query) as q
union
select Graph, [2007],[2008] From (
profit pivot query) as q

 

[John Spencer]

I would try the following

TRANSFORM First(yrSales)
SELECT "Sales" as Graph
FROM qryMktTtls
GROUP BY [Market#], "Sales"
PIVOT [Year] IN ("2005","2006","2007",YTD")

Union ALL

TRANSFORM First(yrProfit)
SELECT "Profits" as Graph
FROM qryMktTtls
GROUP BY [Market#], "Profits"
PIVOT [Year] IN ("2005","2006","2007",YTD")

If that failed then I would create two crosstab queries based on the above and
then use a union of the two queries that looked like

SELECT Graph, [2005], [2006], [2007], [YTD]
FROM FirstCrossTab
UNION ALL
SELECT Graph, [2005], [2006], [2007], [YTD]
FROM SecondCrossTab

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Bob Barrows [MVP]]

Both crosstab queries produce 4 columns of data, 2005, 2006, 2007,
YTD.

That does not matter. By their very nature, crosstabs are not guaranteed
to produce the same number of columns every time. Just because it does
now for your current set of data does not mean they always will.
John's variation of my solution is a little cleaner.

When I try to run it, I get the error msg:

"Syntax error (missing operator) in query expression 'Year

Union

TRANSFORM First(yrProfit)
SELECT "Profits" as Graph
FROM qryMktTtls
GROUP BY [Market#], "Profits"
PIVOT Year'

I tried enclosing Year in square brackets ([Year]), thinking that
maybe Access mistook Year for a function, but that made no
difference, except now there were square brackets in the error msg.

Is it possible for you to rename that field? It IS a reserved keyword
....

 

[ragtopcaddy via AccessMonster.com]

Thank you John & Bob,

I tried this:

TRANSFORM First(yrProfit) AS Amt
SELECT [Market#], "Profits" AS Graph
FROM qryMktTtls
GROUP BY [Market#], "Profits"
PIVOT Year In ("2005","2006","2007","LTM")

UNION ALL

TRANSFORM First(yrSales) AS Amt
SELECT [Market#], "Sales" AS Graph
FROM qryMktTtls
GROUP BY [Market#], "Sales"
PIVOT Year In ("2005","2006","2007","LTM")

And now I get "Syntax error in TRANSFORM statement.", and UNION is
highlighted.

I also tried renaming the "Year" field to "Yr" and got no better results.

I would try the following

TRANSFORM First(yrSales)
SELECT "Sales" as Graph
FROM qryMktTtls
GROUP BY [Market#], "Sales"
PIVOT [Year] IN ("2005","2006","2007",YTD")

Union ALL

TRANSFORM First(yrProfit)
SELECT "Profits" as Graph
FROM qryMktTtls
GROUP BY [Market#], "Profits"
PIVOT [Year] IN ("2005","2006","2007",YTD")

If that failed then I would create two crosstab queries based on the above and
then use a union of the two queries that looked like

SELECT Graph, [2005], [2006], [2007], [YTD]
FROM FirstCrossTab
UNION ALL
SELECT Graph, [2005], [2006], [2007], [YTD]
FROM SecondCrossTab

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

I've read on this group that it's possible to create a Crosstab/Union query.
However, this doesn't work:

[quoted text clipped - 14 lines]

What do I have to do to make this work?