Union query and pivot tables

[ripon via AccessMonster.com]

Hi All:
I have 2 pivot queries wich I am joining with a union. The problem is the
number of columns dont remain equal always for both pivot queries and as a
result, I am getting error in union. So can I put a blank/null for missing
pivot columns to have same number of columns for both queries all the time.
Example:
Transform sum(v_tot -v_land) as om
Select tab1.year,sum(om) as tot1
from tab1 where tab1.year=2005 or tab1.year=2006
and tab1.month=3
group by tab1.year
pivot tab1.code
union
Transform sum(v_tot -v_land) as om
Select tab1.year,sum(om) as tot1
from tab1 where tab1.year=2005 or tab1.year=2006
and tab1.month <=3
group by tab1.year
pivot tab1.code

--Sometimes the tab1.code does not have any data in tab1 and thus give non-
matching columns. Sometimes 1st part has 6 colmuns and 2nd part has 7 columns.
How can I have same num of columns to union them and the column should be in
order.
YEAR--------TOT1---------101--105---110
2005--------50000--------199--250---200
2004---------40000-------50----300--230
Thanks

 

[[MVP] S.Clark]

Can you write the data to a table with a known structure? Use some action
queries to write the data, instead of that Union?

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Jon Ley]

If (and only if) you have a known set of codes that are candidates for your
pivot columns, you can force the pivot data to display all required columns
even if there is no data. I think you need

....pivot tab1.code IN (101, 105, 110, ...)

or if your code is a text field use IN ('101', '105', '110', ...)

If this is not right, create a standard pivot query in design view, open the
properties window for the query and then type in the column headings you
want. Switch to SQL view to get the right syntax.

The only problem with this is that if you get new codes in tab1.code and
forget to add them to your pivot query, you'll never see the data. I don't
think you can use a sub query, as in

pivot tab1.code IN (SELECT DISTINCT code FROM tab1)

but you never know - give it a try!