Variables and counting records

February 18, 201114 yr

I know this is easy for most-but I am struggling. I have a survey with Yes, No, Na check boxes. I want to have FMI count the number of "Yes", count "no" and count "na" while skipping the empty answers for multiple records. I am guessing there is a way to use a variable or global field or something. Right now--I am creating 3 different fields for one question. The actual answer as text (checkboxes). Then a calculation field for giving a numeric value to the answer (if (benefits = "yes";1;0), then summarizing the Total of Benefits.

I know there is an easier way.

February 18, 201114 yr

More details would be useful. In general, if you sort your answers by question and by answer, you can use a single summary field to count the number of answers in each group.

February 21, 201114 yr

Author

What I would like to end up with is a Summary page:

Question 1: 50% answered "yes"; 40% answered "no" and 10% answered "na" of the 50 people answering question 1.

Question 2: 30% answered "yes"; 30% answered "no" and 40% answered "na" of 45 people answering Q2.

Right now I have coded for question 1:

Q1 Text

Q1Scale = If (Q1= "yes"; 1, 0)

Q1RecordCount = Count(Q1Scale)

Q1Total Summary(Number) = Total of Q1Scale(running) **counting yes**

Q1TotalRecord Summary(Number) = Total of (Q1RecordCount) **counting questions that were answered regardless of answer**

Q1Average Calculation(Number) = Q1Total/Q1TotalRecord

This is only giving me the percentage for the yes answers. This just seems so cumbersome to repeat for Q2 then Q3 etc...

February 21, 201114 yr

How about doing it the simple way?

SummaryReport.zip

February 21, 201114 yr

This is only giving me the percentage for the yes answers. This just seems so cumbersome to repeat for Q2 then Q3 etc...

That's because there is a basic data structure problem with your approach. You will need to change your design.

The questions should be separate records, not separate fields.

February 22, 201114 yr

Author

That's because there is a basic data structure problem with your approach. You will need to change your design.

The questions should be separate records, not separate fields.

So to clarify--if I have 40 questions there would be 40 records.

Could you recommend a good instructional manuel or training course.

February 22, 201114 yr

if I have 40 questions there would be 40 records.

Yes, but what's more important here is that if you have 40 questions and 20 people answering them, there should be 800 individual records in the Answers table.