Background: I have 3 tables- Contacts (all information on students is here-name, id etc), Courses (This is connected to contacts via "Concentration" tab- this is a table that lists all of the courses needed for each concentration), and Student_Course (This is a table of a student's Id, Concentration, CourseID and status. This is connected to Contacts table via ID and Concentration. This table lists all of the courses needed for the concentration and then is marked "complete", "needed" or "enrolled".).
I would like to mark a status for each student based on the status of the courses.
ie. when all of the student's courses are marked "Completed," I would like a new status field to change from "In Progress" to "Program Complete." I tried an "if" calculation, but this only calculated on each field If ( Status ≠ "Completed"; "In Progress";"Program Complete" ). I need it to calculated based on ALL courses for the Student_ID.