Request for help with counting.

[Jay Epoch]

Hi. I am new to Filemaker and also new to these Forums, so if this post should be somewhere else please let me know. So far I've been able to figure out a way to do a given task in Filemaker but haven't been able to do the following. I will describe the situation and I really hope someone can help, cause I'm getting frustrated and have no one to go to.

I have a table called Students with a set of fields that are named like this...

Class1 Class2 Class3 ... Class12

And what each 'ClassN' field contains is a user selected item from a list of Classes, which is located in another table called Master Class List. I cannot for the life of me figure out how to generate a field for each Class in this Master Class List that counts how many times that Class was selected in the Students database.

Is this a matter of poor database design or me not knowing file maker or both? Any help or guidance on this issue would be greatly appreciated, as I have no one I know to go to for help. Thanks.

[bruceR]

The Class1, Class2 thing is a dead giveaway, and redesign is definitely in order. However, we will need to know more about what you are trying to do. Most likely you will need at least a Student table, a Class table, and and Enrollment table. The Enrollment tables is a join table - one record having a student ID and and a Class ID.

[LiveOak]

When additional data items require a change to database structure it is generally an indication of incorrect database design. The addition of more classes per student can require the addition of class fields, an indication of incorrect structure.

What you have is a "many to many" relationship between students and Master Class List. One student can take many classes and one class can have many students. This is resolved into two "one to many" relationshps using a "join" table between the two in which each entry is a student/class. Students <--> Student/Class <--> Master Class List. If you do this, the total students taking a class can be computed using the Count(relationship::field) in the Master Class List table acting upon a relationship between Master Class List and Student/Class.

By the way, Classes and Students is the classic example used to demonstrate the need for a join table.

-bd

[Jay Epoch]

Thanks guys! I will look into the join table as a solution to this problem. I appreciate your guidance!