AnsweredAssumed Answered

Calculation fields not all being evaluated in a relational DB

Question asked by LGC on Aug 17, 2011
Latest reply on Aug 18, 2011 by LGC


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!