I have the following table relationships:
I have created ExecuteSQL-based fields in StudentTests table to calculate the raw scores of each Section of each test (Section 1, 2, 3, 4, 5, etc.). It searches the number of correct answers in a specific Test by SectionType for the given Student_ID.
I am creating different lookup conversion tables for these raw scores. Because the 5 sections are ExecuteSQLs instead of say records in a StudentTestSection table, how do I create the relationship/use the conversion table to lookup the converted score to insert into the StudentTests layout? If it was StudentTestSection table records, I would just create the Raw_score relationship between the two tables. That would be easiest, but I am already using a magic key to create TestResponses records and a StudentTests record. I mean, should I be creating a StudentTestSection table and then have the magic key create new records for each TestResponses Section being used OR is there a simpler route?