If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Union query and pivot tables
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200605/1 |
#2
|
|||
|
|||
Union query and pivot tables
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 "ripon via AccessMonster.com" u15564@uwe wrote in message news:5ff8b66f7c1e9@uwe... 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200605/1 |
#3
|
|||
|
|||
Union query and pivot tables
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! "ripon via AccessMonster.com" wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200605/1 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cross tab query construction with Subqueries | Steven Cheng | Running & Setting Up Queries | 7 | February 13th, 2006 06:52 PM |
Nested in-line Query | laura | Running & Setting Up Queries | 0 | February 11th, 2005 12:17 AM |
Form input in query, Union and crosstab query gives error msg | Christian | Running & Setting Up Queries | 2 | November 17th, 2004 05:00 PM |
how to share pivot tables | Will | General Discussion | 0 | October 22nd, 2004 04:33 PM |