Results 1 to 4 of 4
  1. #1
    Queue2 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    1

    Query - Rows to Columns

    I am working to change the results of a report which lists data by row to columns. I want to load the first table below into outlook as this is how it is extracted and have a query run to export the data in the second table format.

    Any thoughts?



    ID First Name Last Name Status Benefit Plan
    0001 Joe Doe Active Medical PPO
    0001 Joe Doe Active Vision EyeQue
    0001 Joe Doe Active Dental TeethBrite
    002 Jane Doe Active Life Dying Payment
    002 Jane Doe Active Extra Life Paid Life
    002 Jane Doe Active LTD Sickness
    003 John Doe Active EAP EAP
    003 John Doe Active STD Short Sickness




    Desired Result
    ID First Name Last Name Status Medical Vision Dental Life Extra Life LTD EAP STD
    0001 Joe Doe Active PPO EyeQue TeethBrite
    002 Jane Doe Active Dying Payment Paid Life Sickness
    003 John Doe Active EAP Short Sickness

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Play with the crosstab query wizard and see if it works for you. Use Max or something other than Sum.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I had to do that once. The data came from the client in a spreadsheet with all info on an employee in one row. Our dB imported the data, processing the rows into the correct tables.

    Then the client wanted to check the employee data in the database, so I had to write a sub that took the data from 8 tables and convert it to one Excel row. There were 27 fields IIRC. Took around 400 lines of code, but each variable I declared was on a separate line so that added to the number of lines.

    I had a temp table that deleted all the data, ran the code, then exported the data from the tmp table back to Excel. (It looked a lot like your desired result...)

    So if the crosstab query doesn't work (and I see no reason it shouldn't), you can convert it using code.

  4. #4
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Using Power Query, here is the Mcode. Bring into PQ, and pivot the data being sure to Not Aggregate the Data.

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"First Name", type text}, {"Last Name", type text}, {"Status", type text}, {"Benefit", type text}, {"Plan", type text}}),
        #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Benefit]), "Benefit", "Plan")
    in
        #"Pivoted Column"
    v A B C D E F G H I J K L
    1 ID First Name Last Name Status Medical Vision Dental Life Extra Life LTD EAP STD
    2 1 Joe Doe Active PPO EyeQue TeethBrite
    3 2 Jane Doe Active Dying Payment Paid Life Sickness
    4 3 John Doe Active EAP Short Sickness

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

Similar Threads

  1. Query Stacking Columns as Rows
    By NSearch in forum Queries
    Replies: 2
    Last Post: 03-10-2019, 04:39 PM
  2. Query with two columns to Rows
    By MrDummy in forum Queries
    Replies: 3
    Last Post: 05-14-2016, 10:03 AM
  3. Replies: 3
    Last Post: 05-06-2015, 06:02 AM
  4. Inverting Columns and Rows in Query
    By jlclark4 in forum Queries
    Replies: 1
    Last Post: 05-28-2013, 12:00 PM
  5. Query to transpose Data in Columns to Rows
    By ace1259 in forum Access
    Replies: 2
    Last Post: 04-10-2012, 01:49 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
  •  
Other Forums: Microsoft Office Forums