How to COUNT survey repsonses.

[apex77]

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

 

[John Spencer]

You have a table structure problem which you MIGHT be able to cure with
a union query. Although with 14 questions this may not work..

The base query would be a UNION query that looked like this
SELECT TheName, "Q01" as QNum, Q01 as Response
FROM tblMain
UNION ALL
SELECT TheName, "Q02" as QNum, Q02 as Response
FROM tblMain
UNION ALL
....
UNION ALL
SELECT TheName, "Q14" as QNum, Q14 as Response
FROM tblMain

Now using that as the source, you could run a query
SELECT Thename, QNum, Response, Count(Response) as Times
FROM qUnion
GROUP BY Thename, QNum, Response

If you can't normalize your data structure you will need a query that is
more like

SELECT TheName
, Sum(IIF(Q01=3,1,Null)) As Q01_3
, Sum(IIF(Q01=4,1,Null)) As Q01_4
, Sum(IIF(Q01=5,1,Null)) As Q01_5
, Sum(IIF(Q02=3,1,Null)) As Q02_3
, Sum(IIF(Q02=4,1,Null)) As Q02_4
, Sum(IIF(Q02=5,1,Null)) As Q02_5
....
FROM tblMain


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

Forgot that you will need to GROUP BY on TheName field.

SELECT [TheName]
, Sum(IIF(Q01=3,1,Null)) As Q01_3
, Sum(IIF(Q01=4,1,Null)) As Q01_4
, Sum(IIF(Q01=5,1,Null)) As Q01_5
, Sum(IIF(Q02=3,1,Null)) As Q02_3
, Sum(IIF(Q02=4,1,Null)) As Q02_4
, Sum(IIF(Q02=5,1,Null)) As Q02_5
....
FROM tblMain
GROUP BY [TheName]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================