Union query and pivot tables

  • Thread starter ripon via AccessMonster.com
  • Start date
R

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
 
J

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top