Variables and counting records

[snaves]

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.

[comment]

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.

[snaves]

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...

[comment]

How about doing it the simple way?

SummaryReport.zip

[bruceR]

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.

[snaves]

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.

[comment]

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.

See also:

http://fmforums.com/forum/topic/45972-another-survey-question/

http://fmforums.com/forum/topic/49072-surveys-and-relationships/page__p__229378#entry229378

Edited February 22, 2011 by comment

[efen]

Does the attached help? It's a file I found on my hard drive - don't know where it's from...

survey.zip