I am struggling to wrap my mind around this probably really simple calculation. Any help would be appreciated. The database I'm developing is for storing student test scores for reading, writing, and math exams. These scores are imported from Excel and stored in a table called TestScores. This table contains columns such as StudentID, TestScore, TestName (reading, writing, math), TestDate, etc. I also have a table called StudentInfo, with fields such as StudentID, Name, Phone, HighestReadingScore, HighestMathScore, HighestWritingScore, etc. These tables are related on StudentID.
I'm trying to calculate the HighestReadingScore field by using the Max function to find the highest score in the table for that student where the test name is Reading. I cannot figure out for the life of me the formula to do this.
Any thoughts? Thanks much!