Results 1 to 10 of 10

How to Partially Transpose A Table

  1. #1
    jrfost is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    8

    How to Partially Transpose A Table

    Hello All,

    I assume this would be a query question, probably crosstab, but I cannot figure it out. If I have a table like this:

    Store Location Desktop Tablet Phone
    Electronics Inc Los Angeles $770 $400 $300
    Devices Corp New York $700 $300 $450



    And I want to "transpose" just the last 3 columns so it looks like this:
    Store Location Device Price
    Electronics Inc Los Angeles Desktop $770
    Electronics Inc Los Angeles Tablet $400
    Electronics Inc Los Angeles Phone $300
    Devices Corp New York Desktop $700
    etc


    I receive an automatic report that looks like the above table and I am trying to convert it to look like the bottom table, to no avail. I feel like it should be easier than what I have tried but I can't get it to work. Does anybody know how to do this?

    Thanks in advance, it is much appreciated.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,638
    The second view is how the table should have been designed in the first place (search on normalization). You can use a union query to get what you want:

    SELECT Store, Location, "Desktop" As Device, Desktop
    FROM TableName
    UNION ALL
    SELECT Store, Location, "Tablet" As Device, Tablet
    FROM TableName
    UNION ALL
    SELECT Store, Location, "Phone" As Device, Phone
    FROM TableName
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,394
    An alternative solution would be to use Power Query/Get and Transform

    Here is the MCode that will do what you want and export to Excel. You can then upload or link to your Access DB
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store", type text}, {"Location", type text}, {"Desktop", Int64.Type}, {"Tablet", Int64.Type}, {"Phone", Int64.Type}}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Store", "Location"}, "Attribute", "Value")
    in
        #"Unpivoted Other Columns"

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,404
    If you use UNION then the first SELECT should include an alias name for the price field: Desktop AS Price.

    There is no wizard or designer for UNION, must type or copy/paste in SQL View of query builder.

    As Alan suggested, might be better to rearrange data before import to Access.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  5. #5
    jrfost is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    8
    Thank you all for your replies.

    Quote Originally Posted by pbaldy View Post
    The second view is how the table should have been designed in the first place (search on normalization). You can use a union query to get what you want:
    Unfortunately the table does not belong to me, so I am unable to change the formatting. And it is given to me via access so I am stuck working within it unless I want to export to Excel and then import back in. I won't have access to the database until Tuesday but I will let you guys know then if I was able to get the solution to work.

    Thanks again for the help I appreciate it.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,638
    The union query will give you what you want then, adding the alias for price as June7 pointed out.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  7. #7
    jrfost is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    8
    Hello All,

    Thank you for the help. I tried using that SQL, changing the names for my tables and it works but with one Caveat. It asks to enter Parameter values for each "Device". If I don't, the device column will be blank. But the pop up box asking for the parameter includes the device in its text Click image for larger version. 

Name:	Parameter.PNG 
Views:	7 
Size:	4.8 KB 
ID:	36963.


    In this case "P-123" is the equivalent of "Phone". If I type P-123 into the box, it will correctly populate the query, but is there a way to do it automatically?


    Thanks again, I really appreciate it.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,638
    What exactly is your SQL? Did you put the fixed text in quotes like I did?
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  9. #9
    jrfost is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    8
    Never mind, just fixed it. I have no idea what was wrong but I just deleted it and retyped it and it worked. Thank you so much for your help and quick responses.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,638
    Glad you got it working. That parameter prompt is basically Access telling you it can't find whatever is listed in the prompt. Often it's just a spelling mistake.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Transpose table
    By jabarlee in forum Queries
    Replies: 5
    Last Post: 07-27-2018, 06:52 AM
  2. Transpose a table using query in Access
    By Skhaliq in forum Access
    Replies: 1
    Last Post: 03-22-2018, 12:02 PM
  3. Transpose Data of single table
    By Atif Mahmood in forum Access
    Replies: 4
    Last Post: 05-05-2016, 03:50 AM
  4. Partially Duplicated Info
    By QuantifyRisk in forum Access
    Replies: 25
    Last Post: 07-17-2014, 12:51 PM
  5. Transpose specific records to table (VBA)
    By KP_SoCal in forum Programming
    Replies: 2
    Last Post: 02-27-2013, 08:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums