I have a reporting tool wiht two (main) tables. One is an attendance summary (Attendance_Utility) with one record per student. The other is a collection of the attendance data (Attendance_Calc) with one record per day per student and typically contains 30000 to 70000 records (number of days * number of students). The tables are related by a students ID (unique in the summary table, many instances in the data table).

For each record in the data table there is a daily status indicator. If it changes from the enrolled state to the withdrawn state a flag is set. If the flag is set I should be able to pull the "Status_Change_Code" for that record from the data table to the summary table. I have gone around in circles for 2 days now trying to do what appears to be a very simple thing ... but to no avail.

Here is the calculation for the field (Status_Change_Code) in the summary table (Attendance_Utility):

Case ( Attendance_Calc::SCC_Flag = 1; Attendance_Calc::SDAYSTATUS; 0 )

SCC_Flag is the status change indicator flag and is 1 or 0 (zero), SDAYSTATUS is the field where the status code resides. SCC_Flag is an unstored calculation and SDAYSTATUS is indexed text.

Is it a bad calculation or a bad table relationship? I've tried so many things that I can't sum them all in this post.

Bob

Ok ... no takers. Maybe someone can help me understand the relationship so that I can get the calculation to work.

This is a 1:M relationship with the primary key of the main table used as a foreign key in the data table. It is my understanding that this should act like a "Find" of related records via this relationship. If this is so, then using a calculation that identifies a particular field with a particular attribute should identify that specific record?

So if the relationship finds 130 records with only 1 having the criteria outlined in the calculation it should return results from that record?

Please,fill me in if I am way out on this!

Bob