NEWBIE: Need help querying survey results

[murso]

I'm a newbie to this forum and Access XP.

Situation: I need to analyze a survey that contains 22 multiple choice questions.

Problem: The 22 questions each have 3 possible choices. I need to calculate how many people chose "A", "B" or "C" for each question. I then need to express the choices in a percentile.

MY (time consuming) Solution: Make 22 separate queries, 1 for each question. Use the results on the report.

End Result: The resulting calculation would be something like this on a generated report (I know how to do the report, but not the query for it) -
How fast do you drive? A) Very fast - 20%
B) Not very fast - 10%
C) Slowly - 70%

Question: Is my solution wrong? Can it be made easier? Can I make ONE query that gives me the result for all 22 questions that can be put on the report? Should i be doing a crosstab query instead?

Thanks for the help!!!

 

[Triton46]

Is your questionaire in Brio? Do you have a database?

You should push your results to the datbase then do a query on the database to calculate the results.

 

[nthg2say]

are you in Oracle? (in sybase look up the 'case' statement)

select sum(decode(QuestionResponse,a,1,0)) Q1a,
sum(decode(Question1Response,b,1,0)) Q1b,
sum(decode(Question1Response,c,1,0)) Q1c,
sum(decode(Question2Response,a,1,0)) Q2a,
sum(decode(Question2Response,b,1,0)) Q2c,
etc, etc...

Then from the result you can:
question 1
answer a % = Q1a/(Q1a+Q1b+Q1c)
answer b % = Q1b/(Q1a+Q1b+Q1c)
answer b % = Q1c/(Q1a+Q1b+Q1c)