Counting Occurrences of a particular response

[Tokyo_Jim]

Hello everyone. I'm an American professor at a Japanese university. I have expertise with Excel but am relatively new to FMP.

 

I am trying to make reports of a student satisfaction survey to return to teachers. There are 25 courses, 14 questions, anywhere from 5 to 20 students per course. All questions were multiple choice, Strongly Agree, Agree, Disagree, or Strongly Disagree. I would like the teacher report to simply report the raw count of course takers who answered Strongly Agree, Agree, etc. for each question. Something like this:

 

Course: American business

Teacher: Mike

 

1. The course goals were clearly presented:   (SA) 7    (A) 5    (D) 1  (SD) 0

2. The course increased my understanding:    (SA) 7    (A) 5    (D) 1  (SD) 1

 

I've set up two tables and joined them:

 

Courses

  Course_ID

  Teacher

 

Responses

  Course_ID

  Question_ID

  Response

 

I've been unable to figure how to count the number of SA or A responses to each question for a Found Set (a course). Should I be using a Summary Field  = Count? This seems to count all fields with a response. That is, all of them. Is there a way to count if response = SA, as I would do in Excel? Do I have the database tables set up correctly? I'm I barking up the wrong tree? Any help or pointers greatly appreciated.

 

  

[Fitch]

In this situation it might be best to simply make four response fields instead of one. The response would be 1 or zero/empty. That would make it simple to total them with summary fields or via a relationship.

[comment]

The simplest way, IMHO, would be to produce a summary report based on the Responses table. Sort the records by CourseID, QuestionID and Response, and show them in a list layout having three sub-summary parts (one for each field in the sort order) and no body part. Place a summary field defined as Count of [ Response ] in sub-summary by Response part. The result should look something like:

 

Course: American business
Teacher: Mike
--------------------------------------------------------
1. The course goals were clearly presented:  
--------------------------------------------------------
(SA) 7    
(A) 5    
(D) 1 
--------------------------------------------------------
2. The course increased my understanding:    
--------------------------------------------------------
(SA) 7    
(A) 5    
(D) 1  
(SD) 1
...

 

Note that zero counts will not appear.

Hint: use a custom value list to sort by the Response field.
 

In this situation it might be best to simply make four response fields instead of one. The response would be 1 or zero/empty. That would make it simple to total them with summary fields or via a relationship.

 

SD.

[Tokyo_Jim]

Thanks for the tips. So far I've had limited success experimenting with both approaches, but I have the feeling I'm missing something obvious.

In the first suggestion (Fitch), I tried making a field for each possible response and a count summary field for each, and put them into an ordinary layout. However, I always end up with counts of fields  for all questions rather than counts of the records related to each question. Is there a calculation function that will total counts of responses for each question separately? I've been unable to make one that works. It would help considerably because the sub-summary report has layout limitations. In two cases, the responses are NOT SA, A, D, or SD and it would help if I could label those responses separately.

 

When trying the sub-summary approach, I could not get labeled sub-summaries as above from a count summary of the response field. I had to create separate fields for each possible response (SA A D SD) and enter a 1 or blank, as suggested by Fitch. This did work, but involved a lot of extra data re-formatting and does not seem to be a very elegant solution. As the project is still in progress, any additional tips or insights greatly appreciated. In particular . .

 

Is there a calculation function I could use to sum the number of SA responses for Question 1 of Course X only?

 

Have I missed something obvious about setting up the sub-summaries, such that I could have avoided all the data re-formatting?

 

Happy New Year and thanks in advance

[comment]

See if this helps:

ProductSurvey.fp7.zip

[Tokyo_Jim]

Thanks for the above. It did help considerably, although I'm still struggling with issues of getting a course to fit on a single page.

[comment]

Well, that is the simplest (and consequently, the fastest) method. It's important to get familiar with it before making it more complex.

 

Now, if you prefer, you can save some vertical space by displaying the response counts in columns rather than rows, using a technique I have described only a few hours ago here:

http://fmforums.com/forum/topic/90692-complex-subsummary-reporting/?p=416305