Calculating Multiple Student Data for Their Teachers

[leeinhaiti]

This feels like a newbie question, so thanks in advance for your patience.

I have a student table related to a teacher table. Multiple students for each teacher.

The student table is simple, it captures:

Gender (0 = Boy; 1 = Girl)

Test Score: 0 = flunked; 1-4.99 = redo year; 5+ = graduates to next grade

I already did a calculation that automatically creates a category (flunk, redo, graduate) based on the test score.

0 = flunk

1 = redo

2 = graduate

However, now that that's done, I need to summarize all the students data for their respective teachers. I'm simply at a loss at how to calculate the following (looking in the attachment might be quicker, but I'll spell it out here):

Teacher fields that I need to calculate:

• number of male & female students (total # of 0s and 1s)

• number of male & female students who flunk (total # of 0s and 1s who got a 0 in the "Promotion_Retake_Abandon" student field)

• number of male & female students who retake (total # of 0s and 1s who got a 1 in the "Promotion_Retake_Abandon" student field)

• number of male & female students who are promoted (total # of 0s and 1s who got a 2 in the "Promotion_Retake_Abandon" student field)

• separate averages for all the students grades based on gender

Thanks in advance, I know this is a lot of stuff to pile into 1 post, but once I get this down I think I'll be set!

Calc_Demo_Clone.zip

[comment]

This would be best done in a report produced from the Students table, summarized by teacher, gender and flunk/pass.

[leeinhaiti]

So there's no way to pull that data from all the students of each teacher into the teacher table? Or is it just a pain?

[comment]

Just a pain.

[leeinhaiti]

This is all going to get exported into STATA at some point, so don't I really need to grab that student data and aggregate it into the teacher records, so that down the line my teacher variables will have all that necessary data?

Besides, I'm a glutton for pain...

[comment]

This is all going to get exported into STATA at some point, so don't I really need to grab that student data and aggregate it into the teacher records

I am not sure. I don't know what format you need - perhaps an export from the student table, with the 'Group by" option checked, might work for you.

You might also aggregate the data by a script run just before the export.

Besides, I'm a glutton for pain...

But I am not - so let me just give you a few quick pointers:

// NUMBER OF STUDENTS

Count ( Students::TeacherID )

// NUMBER OF FEMALE STUDENTS

Sum ( Students::Gender )

// NUMBER OF MALE STUDENTS

the difference between the two above

For the rest, you would need to define either special calculation fields that can be counted or summed, or additional relationships filtered by the relevant criteria. Or write custom functions that iterate over related record and summarize them - see, for example:

http://www.briandunning.com/cf/890