Transpose Access Table / Query

C

Corey Ryan

Hello,
I have been searching for a solution for my problem for about 4 weeks and am banging my head against a wall.

I have 4 Access 2003 queries (Capital Replacement,Maintenance, Operations and Expansion) that have various Asset Items in the first column and the following 20 columns are Year 1, Year 2, Year 3....Year 20. Each query prompts for a start year (eg 2009). Numerous equations in the queries calculate replacement costs at the required year.

I created sub-total queries of all the above queriesgiving me 1 row by 20 column Queries.

My aim is to be able to chart total operation, maintenance, renewal and expansion costs by year (1 to 20)

My question is how do I transpose each single row query so that the initial column headings (Year 1, Year 2 etc form the data in the first column of the transposed table

SumofYear 1 SumofYear 2 SumofYear 3....SumofYear 20
----------------------------------------------------
$ Value 1 $ Value 2 $ Value 3


ie I need the transposed table to look like

Field 1 Field 2
------------------------
SumofYear 1 $ value 1
SumofYear 2 $ value 2
SumofYear 3 $ value 3

etc

I would deeply appreciate any help here at all..

Thanks


Submitted via EggHeadCafe - Software Developer Portal of Choice
Getting Better Information on Unhandled Exceptions In Managed Code
http://www.eggheadcafe.com/tutorial...f-5ff1e261144c/getting-better-informatio.aspx
 
J

John W. Vinson

Hello,
I have been searching for a solution for my problem for about 4 weeks and am banging my head against a wall.

I have 4 Access 2003 queries (Capital Replacement,Maintenance, Operations and Expansion) that have various Asset Items in the first column and the following 20 columns are Year 1, Year 2, Year 3....Year 20. Each query prompts for a start year (eg 2009). Numerous equations in the queries calculate replacement costs at the required year.

I created sub-total queries of all the above queriesgiving me 1 row by 20 column Queries.

My aim is to be able to chart total operation, maintenance, renewal and expansion costs by year (1 to 20)

My question is how do I transpose each single row query so that the initial column headings (Year 1, Year 2 etc form the data in the first column of the transposed table

SumofYear 1 SumofYear 2 SumofYear 3....SumofYear 20
----------------------------------------------------
$ Value 1 $ Value 2 $ Value 3


ie I need the transposed table to look like

Field 1 Field 2
------------------------
SumofYear 1 $ value 1
SumofYear 2 $ value 2
SumofYear 3 $ value 3

etc

I would deeply appreciate any help here at all..
Click to expand...

A "Normalizing Union" query will do this for you:

SELECT [Asset Item], "SumOfYear1" AS Field1, [SumOfYear 1] AS Field2
FROM yourtable
WHERE [SumOfYear 1] IS NOT NULL
UNION ALL
SELECT [Asset Item], "SumOfYear1", [SumOfYear 2]
FROM yourtable
WHERE [SumOfYear 2] IS NOT NULL
UNION ALL
SELECT [Asset Item], "SumOfYear1", [SumOfYear 3]
FROM yourtable
WHERE [SumOfYear 3] IS NOT NULL
UNION ALL
<etc through all 20 columns>

I'm presuming that you do indeed want to track the asset.
 
D

Duane Hookom

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---
 
C

Corey Ryan

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


:

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/tutorial...2d4-853131501049/combinecompressminify-j.aspx
 
C

Corey Ryan

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 hav
worked hard to un-normalize your records and now want them normalized. Thi
is generally done with a union query like
SELECT "SumofYear1" as Field1, Sumofyear1 as Field
FROM queryUnnorma
UNION AL
SELECT "SumofYear2", Sumofyear
FROM queryUnnorma
UNION AL
SELECT "SumofYear3", Sumofyear
FROM queryUnnorma
UNION AL
--- etc--

-
Duane Hooko
Microsoft Access MV

:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Integrate Membership, Roles and Profile Providers into your ASP.NET 2.0 App
http://www.eggheadcafe.com/tutorial...d2-b4d1a17b879a/integrate-membership-rol.aspx
 
J

John Spencer

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
 

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

Similar Threads

Transposing data in Access 2007 Query 1
Query to display highest totals 1
Transpose rows to columns in Access. 3
transpose table query 7
Grouping query 5
SQL JOIN simplifying 5
query to return all records 1
Crosstab Date by Week 1

Top