Multiple column transpose query

Mariyka1

New Member
Hello - I'm trying to transpose multiple columns into two columns in access (second column being the title) access data currently looks like this

Period01 Period02 Period03
120 200 500
110 300 600
200 400 700

I want it to look like this:
January 120
January 110
January 200
February 200
February 300
February 400
ETC

I created this query but I'm getting an error message o incorrect spelling/punctuation



SELECT [Period01] as Month, [January] as value
FROM Summary_ALL
WHERE [Period01] IS NOT NULL


UNION ALL


SELECT [Period02] as Month, [February] as value
FROM Summary_ALL
WHERE [Period02] IS NOT NULL


UNION ALL


SELECT [Period03] as Month, [March] as value
FROM Summary_ALL
WHERE [Period03] IS NOT NULL


UNION ALL


SELECT [Period04] as Month, [April] as value
FROM Summary_ALL
WHERE [Period04] IS NOT NULL


Any insight is appreciated!
 

Joe4

MrExcel MVP, Junior Admin
A few issues.
1. Do not use reserved words like "value" as the name of variables or aliases. This is what is causing your error.
2. If you want to hard-code months, include them in quotes or double-quotes. When you put them in square brackets, it is looking for a field name or input parameter.

So, the first one should look something like:
Code:
SELECT "January" AS Month_Name, [Period01] AS Month_Value
FROM Summary_ALL
[COLOR=#333333]WHERE [Period01] Is Not Null[/COLOR]
Repeat the same logic for the rest of the subqueries in your Union Query.
 
Last edited:

Some videos you may like

This Week's Hot Topics

Top