Multiple column transpose query

[Mariyka1]

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]

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:

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.

 

[Mariyka1]

This worked perfectly! thank you very much

 

[Joe4]

You are welcome!

Just a note - the same logic applies to VBA and names of variables, functions, and procedures there. You will want to avoid using and reserved words.
Here is a list of the reserved words: https://support.office.com/en-us/ar...da-3255-4b12-91a9-f855bdd9c5a2#__toc262648753