AnsweredAssumed Answered

Combining and then Comparing fields from tables?

Question asked by ezeitgeist on Aug 29, 2017
Latest reply on Oct 3, 2017 by ezeitgeist

This forum helped me redesign the setup for a project I had to make it "more flexible". I need could use advice on how to make it functional, hehehe.

 

Setup:

 

3 tables:

 

Test_Results                    Test                         TestQuestions

Test_Results_ID                                              TestQuestions_ID

Test_ID               <-->      Test_ID     <-->        Test_ID

resp_001                                                         SectionNumber

resp_002                                                         QuestionNumber [in Section]

...                                                                     Question_Correct

compare_001

compare_002

...

 

Screen Shot 2017-08-29 at 4.14.47 PM.png

There are different types of Tests that have different numbers of Questions per section. I have response (ie. resp_001) fields that equal the max total number of possible questions.

 

What I need is once a Test_ID is selected for the compare_ fields to compare the resp_ field to the appropriate matching Test Question_Correct if you ordered the Test Questions from 1 on up (ie. Section 1, Question 1 is start; then Section 2, Question 1 is Section 1 Last Question # +1; etc.). I do have TestType table linked to a SectionType table that lists the number of Questions per section, if that helps; though I think something with ordering Section Number and Questions is likely the solution somewhere.

 

Is there a way to create a dynamic list of the 1-215 Questions universal to any Test selected, and then dynamically have that in a compare_ calc field to do the comparing?

 

In a previous iteration I used this in the calc field for comparison, but that was when the questions were matched in non-dynamic tables:

 

Screen Shot 2017-08-29 at 4.39.42 PM.png

Outcomes