Query to populate data from rows to columns

**tscott** · 03-05-2011, 04:15 PM

I have been given survey data with questions in one column and answers in another column. Each survey has 7 questions and therefore each survey is listed on 7 rows like this.

Survey Question Answer
1 Q1 D
1 Q2 S
1 Q3 N
1 Q4 VD
1 Q5 S
1 Q6 VS
1 Q7 Yes
2 Q1 VS
2 Q2 S
2 Q3 N
2 Q4 N
2 Q5 S
2 Q6 VS
2 Q7 No

I’ve been using Excel with named ranges and Vlookups to create a dataset with the 7 questions listed in separate columns with one survey per row like this (abbreviated to simplify example):

Survey Q1 Q2 Q3 Q4 Q5 Q6 Q7
1 D S N VD S VS Yes
2 VS S N N S VS No
Q=Answer to Question, so the Q1 column is the Answers to survey questions 1.

I am attempting to switch my process from Excel to Access 2007, but I'm having trouble getting results I need with one row per survey and answers to each survey question in a separate column.

I can get the results for answers to one question; however, when I add another answer field it doesn’t work. I knew it wasn't that simple, but I don't know what I need to do. I think I need to create a new field with the expression in the field name, but I can’t figure out how to make it work.

I haven't used Access as much as I should and I'm just starting to find my way. Any help with what I should do or what terms I should search for to try to find an answer would be much appreciated.

Thanks, Tammy

**ConneXionLost** · 03-05-2011, 04:57 PM

Hi Tammy,

Try using a crosstab query like this:

TRANSFORM First(tblSurvey.Answer) AS FirstOfAnswer
SELECT tblSurvey.Survey
FROM tblSurvey
GROUP BY tblSurvey.Survey
PIVOT tblSurvey.Question;

Cheers,

**broro183** · 03-05-2011, 05:10 PM

hi Tammy,

EDIT: CL beat me to an answer & the response looks much better than I could offer for an Access solution. However, if the response isn't sufficient..."
/END EDIT

Can you please elaborate on "I am attempting to switch my process from Excel to Access 2007"?
Do you mean you want to remove the excel section completely?
Or do you mean that the surveys are still completed in excel, but you want to move the results into Access?

What do you mean by "I can get the results for answers to one question"?
How are you getting these results (I'm assuming it is "...into Access")?
For example, are you using a "saved import" or some sort of Access query or...?

Can you please upload a sample excel file that contains your actual survey layout (after removing any confidential data)?

Rob

**tscott** · 03-05-2011, 05:15 PM

Oh my. I'm afraid I was hoping for something a little more basic. I've been looking and trying crosstabs, but I'm not sure I'm ready for code (in other words I don't know what to do with the information you provided).

**ConneXionLost** · 03-05-2011, 05:22 PM

Hi Tammy,

It's just SQL code. Open up a blank query and click on the "View" pulldown arrow to select SQL view.

Paste my SQL code into that window, then run the query, or go to query design view. You might need to change the table name, or some of the field names.

Cheers,

**tscott** · 03-05-2011, 06:28 PM

Hi Rob,
Let me see if I can answer your questions.

The surveys are completed online and the exported results are sent to me in Excel.

I have been using Excel up until this point but I've started playing with Access in hopes I could get this part of the data formatted/setup as described faster than Excel. I've been using Vlookups to create dataset from the data the two columns (Question and Answer) to the seven columns (1 column per question). I have a summary worksheet that calculates scores by several different criteria, and we use the data in SharePoint for our team to follow up on the negative survey responses (and then that data will be tied to reporting).

Where I am getting results I expect for only answers to one question is in Access... I created a qry and added a column called Answer with a criteria of"1" which yields all the answers to question 1. (now I'm starting to question everything. LOL). After that seemed to work, I tried creating additional columns for each question the same way but it doesn’t work. As soon as I add the second column, I get nothing in my results. I didn't expect it to work that easy, but I tried. I also tried a couple different expressions Here is one… Q1: IIf([Q]="1",[qryForStep2]![Answer]); I didn't save all of my attemps. When I run, I got a window asking me to input parameters.

I import my excel data to Access and I'm trying to create a query from this data. Actually I import it and then have a query to bring in some other information and limit some data that isn't needed.

I’ve uploaded one file with two worksheets: 1) Sample Data as provided = survey data provided to me; 2) Example of Result Data Setup = shows the format I use in Excel and am trying to create in Access.

Thanks for your help. Tammy