Finding highest values

[Wren]

I have a table where we track students scores.

Students have three attempts to pass there test and each attempt/score is
entered as a seperate line entry.

I ultimately need to identify the highest score for each students test.

The fields i am working with are;

Student Name
Student ID
Test Ttile
Activity date
Score

I have tried to using a duplicates query and then using the totals/min and
max values feature. As this query output is read only, I have no way to
update/identify which score is the higest vale for each test.

I am sure that it is my inexpereince but there must be an easier way to do
this instead of visually checking line by line.

Any direction would be appreciated.

Thank you

Wren

 

[John Spencer]

Since you want to be able to update the results (???) use a correlated
sub-query to identify which record(s) to return

SELECT *
FROM [Your Table] as A
WHERE Score =
(SELECT Max(Score)
FROM [Your Table] as B
WHERE B.[Student ID] = A.[Student ID]
AND B.[Test Title] = A.[Test Title])
ORDER BY [Student Name], [Student ID], [Test Title]

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County