Count field entries selectively

[Guest]

I have a table showing the results of a questionnaire, with one field for
each question, say Q1, Q2, Q3 etc. Say there are up to five possible answers
for each question, A1, A2, A3, A4 and A5. I want to be able to count how
many respondents answered A1 to question 1, how many answered A2 to question
1 etc.

Previously, I have used the following expression in the report or group
footer: =Sum(Abs([Q1]=â€A1â€)) and then copied and adjusted it.

However, I now have a larger questionnaire with 25 questions with say five
possible answers for each. This means I would have to do 125 expressions.

Is there an easier / quicker way to do this?

 

[Guest]

You need a cross tab query.

 

[John Spencer]

Yes, there is a much better way. However, it requires that your database be
structured correctly. For an example of this check out

Duane Hookom's sample survey database at

http://rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

This fully functional application uses a small collection of tables,
queries, forms, reports, and code to manage multiple surveys. Users can
create a survey, define questions, enter pre-defined answers, limit to list,
report results, create crosstabs, and other features without changing the
design of any objects.

Your present structure doesn't lend itself to anything simpler than what you
are doing. If the reponses to the questions are all the same choice - that
is Q1-A1 answer is the same value as Q2-A1 answer -, then you might be able
to create a normalizing Union query and then use that as the source of
cross-tab query. With 25 questions, the union query might fail.

 

[Guest]

Thank you for your reply. I’ve had a look at Duane’s sample database, need
to spend some more time understanding it and may well have more questions
after that!

Yes, there is a much better way. However, it requires that your database be
structured correctly. For an example of this check out

Duane Hookom's sample survey database at

http://rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

This fully functional application uses a small collection of tables,
queries, forms, reports, and code to manage multiple surveys. Users can
create a survey, define questions, enter pre-defined answers, limit to list,
report results, create crosstabs, and other features without changing the
design of any objects.

Your present structure doesn't lend itself to anything simpler than what you
are doing. If the reponses to the questions are all the same choice - that
is Q1-A1 answer is the same value as Q2-A1 answer -, then you might be able
to create a normalizing Union query and then use that as the source of
cross-tab query. With 25 questions, the union query might fail.

I have a table showing the results of a questionnaire, with one field for
each question, say Q1, Q2, Q3 etc. Say there are up to five possible
answers
for each question, A1, A2, A3, A4 and A5. I want to be able to count how
many respondents answered A1 to question 1, how many answered A2 to
question
1 etc.

Previously, I have used the following expression in the report or group
footer: =Sum(Abs([Q1]="A1")) and then copied and adjusted it.

However, I now have a larger questionnaire with 25 questions with say five
possible answers for each. This means I would have to do 125 expressions.

Is there an easier / quicker way to do this?

 

[Guest]

I can’t get the crosstab query to give me what I want. The count I need does
not relate two fields together – I need to count the entries in the fields
themselves.

You need a cross tab query.

I have a table showing the results of a questionnaire, with one field for
each question, say Q1, Q2, Q3 etc. Say there are up to five possible answers
for each question, A1, A2, A3, A4 and A5. I want to be able to count how
many respondents answered A1 to question 1, how many answered A2 to question
1 etc.

Previously, I have used the following expression in the report or group
footer: =Sum(Abs([Q1]=â€A1â€)) and then copied and adjusted it.

However, I now have a larger questionnaire with 25 questions with say five
possible answers for each. This means I would have to do 125 expressions.

Is there an easier / quicker way to do this?