How to query survey responses from a table by question id

[apex77]

I am trying to summarize the results of a survey. I have a table called
tblMain, which looks like:

Name Q01 Q02 Q03
Joe 5 5 3
Jim 4 4 5
John 3 5 5

Joe, Jim, John have been surveyed. Q01, Q02, Q03 are the questions. 3,4,5
are their responses.

The challenge I am facing is how to show the number of 3s, 4s and 5s for
each question. I can show an average easy enough, but am unable to show a
COUNT of the different questions without writing a separate query for each
question (there are actually 14 questions in the real survey).
Thank so much.

 

[pietlinden]

I am trying to summarize the results of a survey. I have a table called
tblMain, which looks like:

Name       Q01      Q02      Q03
Joe           5          5          3
Jim           4          4          5
John         3          5          5

Joe, Jim, John have been surveyed. Q01, Q02, Q03 are the questions. 3,4,5
are their responses.

The challenge I am facing is how to show the number of 3s, 4s and 5s for
each question. I can show an average easy enough, but am unable to show a
COUNT of the different questions without writing a separate query for each
question (there are actually 14 questions in the real survey).
Thank so much.

If you set it up this way, it would be really easy:

(PersonID, QuestionID, Response)

Then it's just a simple crosstab or summary query...
but, since your design is off, you need to write some union queries to
normalize the data...

SELECT Name, "Q01" As Question, [Q01] As Response
FROM tblSurvey
UNION ALL
SELECT Name, "Q02" As Question, [Q02] As Response
FROM tblSurvey
UNION ALL
....
SELECT Name, "Q14" As Question, [Q14] As Response
FROM tblSurvey;