Easiest way to "transpose" data?

[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

 

[Guest]

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

 

[John W. Vinson]

I have a table, laid out as follows:

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

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

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]

 

[Phil Smith]

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

 

[Phil Smith]

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

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

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]