Desperate - Need to turn columns into rows

Posted on 2011-05-13
Last Modified: 2012-05-11
I have 2 Microsoft Access databases.  One table has a users information and another has survey questions and their answers.  I need to get the user info and that person's answers into a row.

The tables look like this:

FirstName LastName Address        SurveyNumber  
Joe            Smith      Maple Street    1                        
Juan           Gonzalez  First Street     2

SurveyNumber     Question   Answer
1                        Height         6 foot
1                        Weight        250
2                        Height         6 foot 1

What query and/or report can I write to make it look like this:

FirstName     LastName   Height    Weight                    
Joe               Smith          6 foot      250
Juan             Gonzalez     6 foot 1  

Thank you very much in advance
Question by:Mikek5665
    LVL 33

    Expert Comment

    select U.FirstName, U.LastName, H.Answer, W.Answer
    from UserInfo U
    join Survey H
      on H.SurveyNumber = U.SurveyNumber
     and H.Question = 'Height'
    join Survey W
      on W.SurveyNumber = U.SurveyNumber
     and W.Question = 'Weight'
    LVL 77

    Accepted Solution

    You need to create a standrad query joining the two tables on SurveyNumber- output all fields.

    Save this query (as say 'qry1')

    Then use the crosstab query wizard to build your desired result from this saved query.  The Name fields go into rows, the Question field into columns and the answer into the summary, choosing the 'First' option for the summary.

    Author Closing Comment

    Thank you so much.  The delay in accepting the answer was because I had to get the work done and out the door first.  Yay!!!

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now