Set value as highest matching value in field in another table
I have a database with several tables, related by (=) serial number. In one table (Testing), there are multiple entries per serial number. In a second table (Priority), each serial number has only one entry. I would like to set the value of a field (Highest test result) in the second table as the highest value in a certain field (Test result) with matching serial number in the related table.
To rephrase, I want to set each entry in 'Priority::Highest test result' as the highest value for the matching serial number from 'Testing::Test result'.
Is this possible? If it is please could someone instruct me or point me to where I can find out how?