Query for Survey Results (1 Viewer)

[mstorer]

I have received survey data and now need to analyze it. We have several questions with answers on a simple 1-5 scale. My table looks something like this:

SURVID Q1 Q2 Q3
1 1 3 4
2 3 5 1
3 3 2 2 etc...

I am to find the percentage of each response for each question. (i.e. Q1 - 3=66%, Q1 - 1=33%) Obviously, some surveys have questions that are not answered so I do not want to contemplate those in my report. I know I can go about this by running a query that will group the response "number" and count the records for that number. However, this works for only one question at a time. Is there any way to whittle this down to one or two queries for the entire survey, rather than an individual query for each question? If not, is there a better table design for this type of project. Thanks in advance for your help...you've come through for me every time so far.

 

[Sohaila Taravati]

Use crosstab queries.

Sohaila

 

[Pat Hartman]

The correct table design is three columns:
SurveyId, QuestionNum, Answer

With the above structure, you can use crosstabs to do all the calculations.

With your current structure, you'll need separate queries for each question.

 

[mstorer]

Ahh yes...I've used crosstabs before but just couldn't see it with my current table structure. I rearranged the table (fortunately I only had 30 or so surveys as this was a trial run). I do have another question. We plan on performing another survey on a much larger scale with questions that will require alpha and numeric responses. Although I haven't run this through a test, (the survey isn't put together yet) the following structure seems as though it would not work as the data in the anser column will consist of different data types:
SurveyId, QuestionNum, Answer

If this is the case, am I back in the same boat as I was before with running a separate query for each question?

 

[Pat Hartman]

It's a little more complicated. I would add an additional field to the answer table so that I could have one numeric and one text. I would create a table that defines the questions and includes an indicator specifying whether the answer is text or user entered numeric. You would need to use this indicator to manage the data entry. Then you'll need two crosstabs. One that "counts" the text responses and a second that "averages" the numeric responses. Use the indicator to get the crosstab queries to restrict themselves to the correct set of questions. Then join the crosstab queries to produce your reports.

 

[mstorer]

Thanks so much Pat. I was thinking something along those lines. I was going to head down the path of placing all of the numeric responses in one table and the text in another. I like your idea a little better. Thanks again for everyone's insight.