Linking two tables' data in a subform

[lauram]

I have a subform that allows entry of student test results. Once the
user inputs the test score and the test ID, I would like the subform to
query a look up table and populate a column on the row that the users
is on to show the level of the student, based on the test result.

TBL StudentTest has StudentID, TestID, TestDate, TestScore
TBLLkup Level has LevelName, TestMaxScore

Is it possible to do this on a subform where there are multiple rows of
student test results displayed? Ideally, the column for student level
would be populated as soon as the user updates the test score column,
and it would not be editable, but it would get stored with the rest of
the data in the row in the Student Test table. Or should I just leave
the test level out of the Student Test table and only query it when
it's needed on a report?

One last thing - when doing the look up, I have to query based on the
student's test score, but the look up table contains only the max test
score for a particular level. So, if a student scores 200, that would
translate to Level 2 where Level 1 max test score is 190 and Level 2
max test score is 210.

[pietlinden]

sounds like you should base the subform on a query and have a formula
to determine the value of the expression you describe. MaxScore? you
can use DMAX or some other method to get that. Why are you storing it?

[lauram]

pi********@hotmail.com wrote:

sounds like you should base the subform on a query and have a formula
to determine the value of the expression you describe. MaxScore? you can use DMAX or some other method to get that. Why are you storing

it?

I have to store the max because the student's level is determine by
what their test score is in relation to a range of test scores. Rather
than store the whole range, I just store the maximum. Then I query to
find the level based on where student test score <= max test score.