count records if two fields match
my database tracks students study status, full time new vs full time continuing. a Masters student is considered full time new until they have successfully completed 6 semesters/terms. a PhD student is considered full time new until they have successfully completed 9 semesters/terms.
i'm trying to automate the change from new to continuing. i created a table to track the semesters/terms completed. 1 record will be completed for each student for each semester/term they complete. my table has a field for the student ID#, the academic year, the program (Masters or PhD) and which semester (Fall, Winter, Summer).
i'm trying to find a way to have FM count the # of records for each student by the program. so if my student currently has 5 records for 5 previously completed semesters of they're Masters degree, then when i add a new record that they have now completed the winter semester for their Masters degree it should show me that this student has now done 6 semesters**. also, if in the fall the student starts their PhD program and i add a record to show they completed a semester of their PhD program it will show the student has now done 1 semester not 7 because it will not count the 6 they did towards their Masters.
this does not take into account those who do not graduate from Masters but simply switch to PhD and are eligible to carry over their Masters semesters to count towards their PhD.... but i think that's probably way too complicated.
**this would trigger the study status to change from full time new to full time continuing.