Calculation fields not all being evaluated in a relational DB
There are 3 related tables:
1) Student: PK-StudentID
2) StudentSpecialProgram: PK-StudentID (I only add the StudentIDs to this table that are associated with a special program)
3) SpecialProgramEnrollment: FK - StudentID
Student to StudentSpecialProgram : 1:1; StudentSpecialProgram to SpecialProgramEnrollment : 1:M
There are over 20 special programs, any (but not all) students could take part in 1 or more special programs. In the StudentSpecialProgram table I have a field for each program possible to indicate if the student takes place in that program. Many students take part in many programs however it appears that only ONE program is flagged per student (possibly the first field calculation to be = true). Since each calculation has it's own field, I don't understand why each one is not being evaluated. The calculation is listed below:
If (SpecialProgramEnrollments::SpecProgID = 954 and SpecialProgramEnrollments::ProgramEntryDate ≥ Date ( 7; 1; 2010);1;0)
The logic appears to be correct because it is populating one of the program fields for each student, but is not evaluating each field (which has the same logic except with it's respective SpecProgID #) individually.
I don't want to add the calculation fields to the Student table, because only 4000 of 22000 student listings would contain indicators for special program enrollment, and I need to generate data for any student with any special program enrollment - the "find" gets ridiculous with all the "or" statements necessary to give me all students with any special program enrollment.
Any ideas would be appreciated!