Jump to content
Sign in to follow this  

Inserting calculated result in another table

Recommended Posts

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.


Share this post

Link to post
Share on other sites

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!


Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Who Viewed the Topic

    1 member has viewed this topic:

Important Information

By using this site, you agree to our Terms of Use.