frequency count

[Jvilla]

I need to do a frequency count.

I have teachers entering data on 3 student assessments.

Each teacher has 24-30 students and must enter a number from 1 to 4 for each student on each of the 3 assessments. (fields are student1_a1, student2_a1, ....)

I need calculation fields which count the numbers of 1s, 2s, 3s, and 4s for each of the 3 assessments, but don't know how to do this in filemaker.

Any help would be appreciated.

[Fitch]

I think what you're asking for is Summary fields. I'm not sure whether your description calls for a Count or a Total. Count simply counts the records that have data in a given field; Total gives you the sum of a Number field from a group of records.

Once you've defined your Summary fields, put them on a Sub-Summary Part, that is sorted by score. You will then see your data in Preview mode when you have sorted by student. You won't see the Sub-Summary Part in Browse mode or when not sorted correctly.

[Jvilla]

I think I do need a Summary field--I used to do something like this with Excell, but don't know how to get Filemaker to do it (we had to count the number of students by ethnicity and gender across departments).

Count won't work as it will count all the non-empty fields (no field should be empty unless the student was absent), and Sum won't as it will just add all the fields--I need to count the number of students' who earned a 4, 3, 2, or 1.

I think I need 4 summary fields which use a sum and an if statement--but don't know how to write it. It would be logically something like this:

sum across student1_e1 through student37_e1 if student1_e1 = 4, then 1, otherwise 0

Does anyone know the syntax for that sort of thing?

[Jvilla]

I think I do need a Summary field--I used to do something like this with Excell, but don't know how to get Filemaker to do it (we had to count the number of students by ethnicity and gender across departments).

Count won't work as it will count all the non-empty fields (no field should be empty unless the student was absent), and Sum won't as it will just add all the fields--I need to count the number of students' who earned a 4, 3, 2, or 1.

I think I need 4 summary fields which use a sum and an if statement--but don't know how to write it. It would be logically something like this:

sum across student1_e1 through student37_e1 if student1_e1 = 4, then 1, otherwise 0

Does anyone know the syntax for that sort of thing?

[tlsparker]

I suggest changing your data structure somewhat:

Instead of having a separate field for each student/assessment combination, you can do this all with three fields (in FileMaker it is cumbersome to summarize data across fields; it is much easier to summarize across records).

The fields would be:

Student (unique number for each student)

Assessment(number from 1 to 3)

Rating (number from 1 to 4 or blank)

You could also have fields for teacher, school, class, and whatever other categories you use.

To count students who have been rated, create a calculation field "Rated" =

If(IsEmpty(Rating), 0, 1)

Then, create a summary field = Total(Rated).

By placing this summary field within different sub-summary parts on your report layouts, you can summarize for whichever categories you need the information.

Remember that you need to sort and enter preview mode to view sub-summaries.

If you need to work with the count data in further calculations, there are methods to create the same summary information using self-relationships within the file, or using relationships from a different file.

Good luck!

Tom

[email protected]

http://www.tgparker.com/filemaker

[Fitch]

I think the Count function will do what you want, but you must SORT by score, or rating, whatever you're calling it. Put the summary/count field on a Summary PART.

With the summary field on the part, in Preview mode, and sorted by score, you will see the total number of blanks, then the number of 1's, then the total 2's etc. If you don't want to see the blanks, then Omit them with a Find before you run the report.

Summaries are not always intuitively obvious for FileMaker beginners, but they are very powerful. The key, again, is that they only show up when you Preview or Print, and then only if you have performed the Sort corresponding to the Layout Part.