How to query survey responses

[rscott927]

I am trying to query a survey repsonse table called tblMain. It look like this:

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

I am trying to show the number of 3s, 4s, 5s for each question (Q01, Q02,
Q03) in a report, but am unsure about how to query the table, WITHOUT having
to create a seperate query for each response (there are 14 questions in the
real table).
Thank you

 

[Douglas J. Steele]

The problem you're having is that your table isn't properly normalized. Q01,
Q02, Q03 is called a repeating group, and should be eliminated: your data
should be:

Name Question Answer
Joe 1 3
Joe 2 5
Joe 3 6
Jim 1 4
Jim 2 4
Jim 3 4
John 1 5
John 2 3
John 3 3

You can salvage what you have, though, by using a UNION query to normalize
the data:

SELECT Name, 1 As Question, Q01 AS Answer
FROM MyTable
UNION
SELECT Name, 2, Q02
FROM MyTable
UNION
SELECT Name, 3, Q03
FROM MyTable

Now it should be easy to query the data to get what you want.

 

[Douglas J. Steele]

Just noticed that you provided the name of your table.

That means your query would actually be:

SELECT Name, 1 As Question, Q01 AS Answer
FROM tblMain
UNION
SELECT Name, 2, Q02
FROM tblMain
UNION
SELECT Name, 3, Q03
FROM tblMain
UNION
....
UNION
SELECT Name, 14, Q14
FROM tblMain