A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Transpose Access Table / Query



 
 
Thread Tools Display Modes
  #1  
Old December 8th, 2009, 11:50 PM posted to microsoft.public.access.queries
Corey Ryan
external usenet poster
 
Posts: 3
Default 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  
Old December 9th, 2009, 01:06 AM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:50 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.