To count related records, the count function can count them. Count (RelatedTable::Field ) will return a count of all related records where Field is not empty.
An IF or Case function can check both the count and the student type(PHD or masters) to show the status that is appropriate.
To not count records from a previous record, set up a relationship that excludes them or count a field that is empty for those records.
ok this sounds good in theory, but i have no idea how to write a calculation using IF or CASE to make it count the number of records for each student ID for each program level.
i'm not sure what you mean by relationship that excludes other student ID's and empty fields.
ok i created two calculations fields, one to count the semesters with MSc as the program and another to count the semsters with PhD as the program.
I should be getting MSc = 6 and PhD = 3, but i'm not, i'm getting MSc = 9
Count MSc = Case ( Student Transcripts::Program = "MSc" ; Count (Student Transcripts::Semester) ; Student Transcripts::Program = "PhD" ; "0" )
Count PhD = Case ( Student Transcripts::Program = "PhD" ; Count (Student Transcripts::Semester) ; Student Transcripts::Program = "MSc" ; "0" )
My table has the field "Program" with a drop down of "MSc" or "PhD" and my field Semester is just a text box, i have Fall Winter or Summer written in this field.