Counting

[andy_42 via AccessMonster.com]

Hello,

I am having difficulty counting numbers within a very large table based on
survey results.

The table is set as follows subjectname, q1a, q1b, q1c, q1d...q5e
Values for each question range from 1-5 and the field could be empty if no
response was given.

I have tried the something similar to the following and it is not counting
correctly.

Select subjectname, count(iff(q1a=5 or q1b=5 ... or q5e=5, 1, 0))
from table
group by subjectname

My desired outcome will be to display the total 5's, Total Responses for the
subject (count where the response is in 1-5), and from this I can calculate
percentages within each rating (15% of participants rated the subject as a 5)

Any help is appreciated. Thanks!

 

[Guest]

Andy,

First off, your data is in the wrong format for this kind of analysis. You
are setup like a spreadsheet, not a database. Questionnaire databases should
have a structure similiar to:

Subject Question Response
Ted Q1 5
Ted Q2 3
Ted Q3 4
Ted Q4 5
Ted Q5 3

With your data in this format, you can do a simple crosstab query with the
subjectName as the RowHeader, the Response as the Column header, and
Count(SomeField) as the Value.

To get your data into this format, you could write a Normalization query
that would normalize your data. It would look something like:

SELECT SubjectName, "q1a" as Question, q1a as Response FROM yourTable
UNION ALL
SELECT SubjectName, "q1b", q1b FROM yourTable
UNION ALL
SELECT SubjectName, "q1c", q1c From yourTable

This is a union query, and can only be written in the SQL view, although I
would start out in the query grid to get the first select statement. If you
have a lot of fields, it will take a while to build this query, but once you
have done so, you can save it.

HTH
Dale