Tallying an evaluation data in Access

  • Thread starter Thread starter Missi Sargnet
  • Start date Start date
M

Missi Sargnet

I have created an evaluation questionnaire with 9 questions for people who
attend classes. Each classes has a unique ID but will have several responses.
Four of the questions have drop down responses of Yes, No or Blank. The
other 5 questions are on a rating scale of 1 to 5. If I create a query for
each question I can use the group by and count to find out my totals (i.e. 5
yes, 4 no, 1 blank) for each question separately. But I cannot figure out how
to join them into a single query so that I can summarize all the questions on
one report? Can this be done without the use of SQL?
 
I have created an evaluation questionnaire with 9 questions for people who
attend classes. Each classes has a unique ID but will have several responses.
Four of the questions have drop down responses of Yes, No or Blank. The
other 5 questions are on a rating scale of 1 to 5. If I create a query for
each question I can use the group by and count to find out my totals (i.e. 5
yes, 4 no, 1 blank) for each question separately. But I cannot figure out how
to join them into a single query so that I can summarize all the questions on
one report? Can this be done without the use of SQL?
Click to expand...
The report should do this (with the sum() function), while the query
only contains the values the report needs.

I would make a query on your answers table (without any groupings) and
for every question with Y/N/B answer I would create three calculated
fields, one for Y, one for N, and one for B. The questions with 1/5
answer are no problem.

So let say the name of the field with the answer to question 1 is Q1,
and that Q1 and Q2 are of the Y/N/B type and Q3 of the 1/5 type, then
put in the query:

Classes
Q1
Q1Y: iif([Q1]="Yes",1,0)
Q1N: iif([Q1]="No",1,0)
Q1B: iif([Q1]="",1,0)
Q2
Q2Y: iif([Q2]="Yes",1,0)
Q2N: iif([Q2]="No",1,0)
Q2B: iif([Q2]="",1,0)
Q3

You save the query and create a report on this query, grouping by
Classes.
Then at the end of the "Classes" grouping you put report fields with
these values:

=sum(Q1Y) with label "Question 1 Total Yes"
=sum(Q1N) with label "Question 1 Total No"
=sum(Q1B) with label "Question 1 Total Blank"
=sum(Q2Y) with label "Question 2 Total Yes"
=sum(Q2N) with label "Question 2 Total No"
=sum(Q2B) with label "Question 2 Total Blank"
=sum(Q3) with label "Question 3 Total"

This should answer your question
Marco P
 
Thank you SO much! I knew it could be done!

Marco Pagliero said:
I have created an evaluation questionnaire with 9 questions for people who
attend classes. Each classes has a unique ID but will have several responses.
Four of the questions have drop down responses of Yes, No or Blank. The
other 5 questions are on a rating scale of 1 to 5. If I create a query for
each question I can use the group by and count to find out my totals (i.e. 5
yes, 4 no, 1 blank) for each question separately. But I cannot figure out how
to join them into a single query so that I can summarize all the questions on
one report? Can this be done without the use of SQL?
Click to expand...
The report should do this (with the sum() function), while the query
only contains the values the report needs.

I would make a query on your answers table (without any groupings) and
for every question with Y/N/B answer I would create three calculated
fields, one for Y, one for N, and one for B. The questions with 1/5
answer are no problem.

So let say the name of the field with the answer to question 1 is Q1,
and that Q1 and Q2 are of the Y/N/B type and Q3 of the 1/5 type, then
put in the query:

Classes
Q1
Q1Y: iif([Q1]="Yes",1,0)
Q1N: iif([Q1]="No",1,0)
Q1B: iif([Q1]="",1,0)
Q2
Q2Y: iif([Q2]="Yes",1,0)
Q2N: iif([Q2]="No",1,0)
Q2B: iif([Q2]="",1,0)
Q3

You save the query and create a report on this query, grouping by
Classes.
Then at the end of the "Classes" grouping you put report fields with
these values:

=sum(Q1Y) with label "Question 1 Total Yes"
=sum(Q1N) with label "Question 1 Total No"
=sum(Q1B) with label "Question 1 Total Blank"
=sum(Q2Y) with label "Question 2 Total Yes"
=sum(Q2N) with label "Question 2 Total No"
=sum(Q2B) with label "Question 2 Total Blank"
=sum(Q3) with label "Question 3 Total"

This should answer your question
Marco P
Click to expand...
 
Back
Top