Query in Access (Transpose)

[eilob]

Hi All, I have the following table:

Week1 Week2 Week3 Week4 Week5 Week6 Week 7
0.49 0.48 0.46 0.45 0.49 0.48 0.46

and need to change it to this layout:

Week Margin
Week1 0.49
Week2 0.48
Week3 0.46
Week4 0.45
Week5 0.49
Week6 0.48
Week7 0.46

Had tried crosstab query and other methods but cant do it

Any ideas

Thanks!

 

[MichaelRed]

inital responses will include

" ... NORMALIZE ..."

. Ignore these at your peril.

crosstab is the easiest way,

next responses will include

" ... post your crosstab query ... "

since the crosstab is relatively easy to make following the help try that in the meantime




MichaelRed

 

[eilob]

Any examples? Had tried that but cant get that layout..

Cheers

 

[PHV]

Use a normalization union query:

SELECT 'Week1' AS Week, Week1 FROM yourTable
UNION SELECT 'Week2', Week2 FROM yourTable
...
UNION SELECT 'Week7', Week7 FROM yourTable

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, sorry for the typo

SELECT 'Week1' AS Week, Week1 AS Margin FROM yourTable
UNION SELECT 'Week2', Week2 FROM yourTable
...
UNION SELECT 'Week7', Week7 FROM yourTable

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[eilob]

THANKS A LOT PHV

That works great!