Easiest way to "transpose" data?

  • Thread starter Thread starter Phil Smith
  • Start date Start date
P

Phil Smith

I have a table, laid out as follows:

Acct, Year, period1, period2, period3, period4,...through period13
Sales 2006 $1000 $2500 $3100 $1250

What is the easiest way to return each record as 12 records,

Acct Year Period, Amount
Sales 2006 Period1 $1000
Sales 2006 Period2 $2500
Sales 2006 Period3 $3100
Sales 2006 Period4 $1250


I can't mess with the table structure, and I actually have a bunch of
different tables I will need to view in a similar fashion.

I can figure ways to do it, but I am sure some of the genius level
talent here has an easy way to do this?

Phil
 
If we were geniuses we would have good jobs and not be trolling around Access
forums :)
I can think of a brute force method which is to do a union of 12 queries
each of which retrieves a single column.
I would not waste brain power thinking of an elegant way to solve that
problem.
Whoever designed the table structure was very misguided.

-Dorian
 
I have a table, laid out as follows:

Acct, Year, period1, period2, period3, period4,...through period13
Sales 2006 $1000 $2500 $3100 $1250
Click to expand...

well... you have a decent spreadsheet, but a lousy table.
What is the easiest way to return each record as 12 records,

Acct Year Period, Amount
Sales 2006 Period1 $1000
Sales 2006 Period2 $2500
Sales 2006 Period3 $3100
Sales 2006 Period4 $1250
Click to expand...

A Normalizing Union Query is about the only practical way to do this: in the
SQL window enter

SELECT [Acct], [Year], "Period1" AS Period, [Period1] FROM yourtable
UNION ALL
SELECT [Acct], [Year], "Period2" AS Period, [Period2] FROM yourtable
UNION ALL
SELECT [Acct], [Year], "Period3" AS Period, [Period3] FROM yourtable
UNION ALL
<etc through all twelve? or thirteen? not clear>

You can base an Append query upon this stored UNION query if you wish to
populate a properly normalized table.

I'd suggest changing the name of the field Year though - that's a reserved
word and can cause trouble.

John W. Vinson [MVP]
 
Dont' sell yourselves short. I sometimes help out the occasional
problem with the simple stuff, but I gave gotten quite a bit of high
level help here. As far as the Union query, I had thought of that as
well, and was just hoping for something a little easier.

The table structure is legacy, and I can not do too much about it. When
this table first came into being, the entire company was running off of
a shared 80286 runing Unix. The things I am hoping to do with this
table were not even possible back then.

Thanx
 
Yep to all. Thanx

I have a table, laid out as follows:

Acct, Year, period1, period2, period3, period4,...through period13
Sales 2006 $1000 $2500 $3100 $1250
Click to expand...


well... you have a decent spreadsheet, but a lousy table.

What is the easiest way to return each record as 12 records,

Acct Year Period, Amount
Sales 2006 Period1 $1000
Sales 2006 Period2 $2500
Sales 2006 Period3 $3100
Sales 2006 Period4 $1250
Click to expand...


A Normalizing Union Query is about the only practical way to do this: in the
SQL window enter

SELECT [Acct], [Year], "Period1" AS Period, [Period1] FROM yourtable
UNION ALL
SELECT [Acct], [Year], "Period2" AS Period, [Period2] FROM yourtable
UNION ALL
SELECT [Acct], [Year], "Period3" AS Period, [Period3] FROM yourtable
UNION ALL
<etc through all twelve? or thirteen? not clear>

You can base an Append query upon this stored UNION query if you wish to
populate a properly normalized table.

I'd suggest changing the name of the field Year though - that's a reserved
word and can cause trouble.

John W. Vinson [MVP]
Click to expand...
 
Back
Top