Results 1 to 4 of 4

Thread: Multiple column transpose query

  1. #1
    New Member
    Join Date
    Nov 2016
    Posts
    38
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Multiple column transpose query

    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!

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,738
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Multiple column transpose query

    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
    WHERE [Period01] Is Not Null
    Repeat the same logic for the rest of the subqueries in your Union Query.
    Last edited by Joe4; Aug 28th, 2019 at 01:17 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Nov 2016
    Posts
    38
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple column transpose query

    This worked perfectly! thank you very much

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,738
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Multiple column transpose query

    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/art...__toc262648753
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •