I have a student table related to a teacher table. If a student is a male, he gets a 0. If a student is a girl, she gets a 1.

In my teacher table, I wanted to create three basic calculations to determine the # of boys and girls in each teacher's class, however, what I thought would be straightforward calculations just aren't resulting in ANY data whatsoever.

There are three fields I have calcs for in my teacher table:

Total male students

Total female students

Total Students

My thought process was:

Total_Female_Students = Sum (Students::Gender)

Total_Students = Count (Students::Teacher_IDfk)

Total Male Students = Total_Students - Total_Female_Students

At first, I thought the correct syntax for the Count calculation was Count (Students::Teacher_ID)

However, this syntax resulted in the error "the specified field cannot be found", because I don't have a field of Teacher_ID in the student table, just a Teacher_IDfk field.

Any suggestions? I'm sure it's right in front of me, and it's driving me crazy! All I'm staring are blank fields right now--clearly the calculations aren't working, but I'm not sure why...either incorrect syntax, or some sort of relationship problem?

Thanks in advance!

You should have posted this in the original thread:

http://fmforums.com/forum/showtopic.php?tid/211095/

Students::TeacherID is the name I used in the example for the matchfield in the Students table (the foreign key to the Teachers table). In your implementation, you need to use your own field/table names.

Note that these calculation fields need to be in the Teachers table.

Edited September 16, 200916 yr by Guest

try using the Summary field type for field 1 and 2 instead of functions Count() and Sum()

Sorry about the misplacement of the post.

In the teacher field there's Teacher_ID

In the student field there's Teacher_IDfk

Count ( Students::Teacher_IDfk )

I thought my syntax was correct, but I'm not getting anything. I really have no idea how to brainstorm possible reasons this is getting tripped up. Any help would be very, very appreciated. Thanks for your patience!

I'm confused. When I try the sum option, it only lets me sum fields within the teacher table, which doesn't help, as I want to sum records in the student table. These two tables are related, yet I can't sum student records in the teacher table.

Am I doing something wrong?

Sorry everyone. I figured it out. Human error strikes again. Thank you for your patience and feedback.