Multiple column transpose query

Mariyka1

New Member
Joined
Nov 1, 2016
Messages
38
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!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,156
Office Version
365
Platform
Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,089,897
Messages
5,411,091
Members
403,340
Latest member
fred23

This Week's Hot Topics

Top