Also, if you ever decide you need to preserve historical level labels and apply new thresholds only to future test takers, I have a solution for that as well that requires only a slight modification to the setup.
But even with your current setup, since you have to link every record from Table A to all 3 levels in Table B in order for this to work, I’d suggest flipping your Table B to run horizontally rather than vertically…
Here’s what I mean -
Instead of having 3 rows in Table B, one for each “Level” you want to define, I’d have 1 row in Table B, called “Levels”. Then, I’d have an integer COLUMN for each Level - “Intermediate Threshold Score”, “Advanced Threshold Score”, and “Expert Threshold Score”.
Then you only have to link each record in Table A to the one “Levels” record from Table B. That will work much better with what I described above.
… actually🤔… what I described above REQUIRES the setup I just described – a single record (row) in table B that ALL records in Table A are linked to. The link has to be established in order for the Lookup fields to be able to pull in your threshold values for each level.
