Query for Survey Results

[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

 

[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?

 

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