Setting column in one table to the Max value in a column from another table

[Todd Martin]

OK, I'm having some trouble updating the values of a column in
one table to the maximum value of a column in a different table.
Here's the relevant table design:

tblStudentInfo
nStudentNum
nStudentLevel

tblTestCandidates
nStudentId
nTestLevel
nResult
nTestEvent

What I'm trying to do is set tblStudentInfo.nStudentLevel for each
student equal to the maximum nTestLevel in tblTestResults where
nTestEvent matches a provided value and nResult is equal to 1.

For a given test event, a student may take multiple tests, but cannot
pass level (n+1) if level n has not been passed.

I can find the maximum value for ALL students with the query

SELECT Max(tblTestCandidates.nTestLevel) AS MaxLevel
FROM tblStudentInfo, tblTestCandidates
WHERE ((tblStudentInfo.nStudentNum=tblTestCandidates.nStudentId) AND
(nTestEvent=4) AND (nResult=1));

but I need to be able to get the max for each student and then set
that value as tblStudentInfo.nStudentLevel.

Any suggestions, as well as a suggestion for a good book on writing
SQL queries, would be appreciated.

Thanks,

--tdm

 

[[MVP] S. Clark]

If you try a lot of Update queries, eventually you'll run into the
'Operation must use an updateable Query' error.

So, because of that error, try the following...
1. Change your current query to a make table query. Run the query to make
the table.
2. To the new table, set a primary key, or unique index.
3. Create a new query, based on the new table, that will update
tblStudentInfo, for the matching StudentID.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[John Verhagen]

Try this:
First run this query:
SELECT tblTestCandidates.nStudentId, Max(tblTestCandidates.nTestLevel) AS
MaxOfnTestLevel INTO tblTestCandidatesMax
FROM tblTestCandidates
WHERE (((tblTestCandidates.nResult)=1) AND
((tblTestCandidates.nTestEvent)=4))
GROUP BY tblTestCandidates.nStudentId;

and then this one:
UPDATE tblStudentInfo INNER JOIN tblTestCandidatesMax ON
tblStudentInfo.nStudentNum = tblTestCandidatesMax.nStudentId SET
tblStudentInfo.nStudentLevel = [MaxOfnTestLevel];