Query to populate data from rows to columns

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

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).

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

Rob, here's the attachment.

CL, I'll see what I can figure out with your code (probably tomorow before I can focus on it).