4 Replies Latest reply on Jan 2, 2014 7:13 PM by JamesSick

    Count of Occurrences of element in a field



      Count of Occurrences of element in a field


           Hello everyone,


           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 AgreeAgreeDisagree, or Strongly Disagree. I would like the teacher report to simply report the raw count of course takers who answered Strongly AgreeAgree, 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:











           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.

        • 1. Re: Count of Occurrences of element in a field

               Your reference to a "Question_ID" field suggests that you actually have more tables, such as a table of questions that you link to the responses table. Is that the case?

               Do you have responses in the form of one response to one question per response record or do you have multiple response fields in a single record such that you have one response record for each person responding to your survey?

          • 2. Re: Count of Occurrences of element in a field

                 Thank PhilMod for replying.

                 My data has been collected using Google forms and is currently in a spreadsheet, one response record for each person responding. Some other forum posts I've been studying have indicated I would be better off with one response to one question with a field to identify the question number and the course it belongs to. I can easily transpose the rows and columns in Excel to import the data in this form if need be. I'm trying to set up a two or three record template that does what I need first, before rearranging and importing data.

                 I tried making a third table with 9 records: Question_ID, SA, A, D, SD, SA-count, A_count, D_count, SD_count. But that doesn't seem to do what I want either. I feel I might be on the wrong track altogether. I've attached an image of the relationship graph.


            • 3. Re: Count of Occurrences of element in a field

                   Using a data model where each response to a question is a separate record will make counting different responses to each question much easier to set up. You may have seen this thread already, but I'd set up a questionaire DB to follow the tables/relationships shown here: Need aid on generating a report from a survey layout.

                   With one record to each question response, you can set up a summary report that groups records first by Question ID and then by the specific response. Summary fields can count the records and by putting them in sub summary layout parts, one summary field can count each different response to each different question.

                   Here's a tutorial on summary reports that may provide you with some ideas: Creating Filemaker Pro summary reports--Tutorial

              • 4. Re: Count of Occurrences of element in a field

                     Thank you PhilMod. I'll read through the above links and try again.