I am working with a "class" database. I have a table that has the following fields.
Record Type: Which can contain - "exam", "preparation", or "diagnostic"
Teacher: The teacher is assigned only if the class is a "preparation" class or a "diagnostic" class
Examiner: An examiner is assigned only if the record type is "Exam"
Rater: The rater is assigned only if the record type is "Exam"
Student: The name of the student
When I create a new record, If the record type = "exam", then both the "examiner" and "rater" fields are required. However, if the examiner and/or rater selected has been the "teacher" in any previous records for this specific student, in either a "preparation" class or a "diagnostic" class, then I want to flag that and I do not want to allow the creation of this record. In this case, the user must select a different examiner or rater. With each new record, the examiner or rater cannot have previously taught any of the preparation or diagnostic classes.
I'm not sure if this is best accomplished using a script that searches the records or perhaps using a relationship or using the validate record on creation option. Any suggestions in the best method to accomplish this would be appreciated.