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
|
|||
|
|||
Transpose Access Table / Query
Hi Duane,
Thanks for your reply. I have been trying to get this to work for a few days without success so I have broken it down to a simple problem. I have a 3 column table DATA1 DATA2 DATA3 ----------------- 3 5 8 With aim of obtaining Field1 Field2 -------------- Data1 3 Data2 5 Data3 8 I am new to Union queries and am assuming I have done something fundamentally wrong! What I have done is.. -Created my initial 3 column table and called it Z -Ran New Queries in Design View -Closed "show table" without opening anything -Query Menu--SQL Specific--Union -Entered the following Code SELECT "DATA1" AS Field1,DATA1 AS Field2 FROM Z UNION ALL SELECT "DATA2",DATA2 FROM Z UNION ALL SELECT "DATA3",DATA3 FROM Z UNION ALL -Saved and ran the query and got the message "Syntax Error in Query. Incomplete Query Clause" Thanks for your time Duane Hookom wrote: It might help if you provided the actual table structures. 04-Dec-09 It might help if you provided the actual table structures. It seems you have worked hard to un-normalize your records and now want them normalized. This is generally done with a union query like: SELECT "SumofYear1" as Field1, Sumofyear1 as Field2 FROM queryUnnormal UNION ALL SELECT "SumofYear2", Sumofyear2 FROM queryUnnormal UNION ALL SELECT "SumofYear3", Sumofyear3 FROM queryUnnormal UNION ALL --- etc--- -- Duane Hookom Microsoft Access MVP "Corey Ryan" wrote: Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice Combine/Compress/Minify JS and CSS files in ASP.NET MVC http://www.eggheadcafe.com/tutorials...sminify-j.aspx |
#2
|
|||
|
|||
Transpose Access Table / Query
Drop the last UNION ALL. You use UNION or UNION ALL between the queries or
tables you are combining.- John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Corey Ryan wrote: Hi Duane, Thanks for your reply. I have been trying to get this to work for a few days without success so I have broken it down to a simple problem. I have a 3 column table DATA1 DATA2 DATA3 ----------------- 3 5 8 With aim of obtaining Field1 Field2 -------------- Data1 3 Data2 5 Data3 8 I am new to Union queries and am assuming I have done something fundamentally wrong! What I have done is.. -Created my initial 3 column table and called it Z -Ran New Queries in Design View -Closed "show table" without opening anything -Query Menu--SQL Specific--Union -Entered the following Code SELECT "DATA1" AS Field1,DATA1 AS Field2 FROM Z UNION ALL SELECT "DATA2",DATA2 FROM Z UNION ALL SELECT "DATA3",DATA3 FROM Z UNION ALL -Saved and ran the query and got the message "Syntax Error in Query. Incomplete Query Clause" Thanks for your time Duane Hookom wrote: It might help if you provided the actual table structures. 04-Dec-09 It might help if you provided the actual table structures. It seems you have worked hard to un-normalize your records and now want them normalized. This is generally done with a union query like: SELECT "SumofYear1" as Field1, Sumofyear1 as Field2 FROM queryUnnormal UNION ALL SELECT "SumofYear2", Sumofyear2 FROM queryUnnormal UNION ALL SELECT "SumofYear3", Sumofyear3 FROM queryUnnormal UNION ALL --- etc--- -- Duane Hookom Microsoft Access MVP "Corey Ryan" wrote: Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice Combine/Compress/Minify JS and CSS files in ASP.NET MVC http://www.eggheadcafe.com/tutorials...sminify-j.aspx |
Thread Tools | |
Display Modes | |
|
|