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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

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,902
Messages
5,411,120
Members
403,342
Latest member
faizanhamied

This Week's Hot Topics

Top