Access: How do I query for EACH student's highest exam score- NOT.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a class of 20 students, who have taken 5 tests.
I have the students' names & test scores in the tables.
I want to set up a query which outputs ALL the students' names in one
column, and each of their top scores next to it.
 
SELECT LastName, FirstName, Max(Score) as TopScore
FROM tblTestScores
GROUP BY LastName, FirstName;
 
Create a query that includes the student names and testscores. Click on the
Sigma button in the toolbar at the top of the screen. Under tests cores,
change Group By to Max.
 
Duane Hookom said:
SELECT LastName, FirstName, Max(Score) as TopScore
FROM tblTestScores
GROUP BY LastName, FirstName;
Click to expand...

Alternatively:

SELECT T1.LastName, T1.FirstName, T1.Score as TopScore
FROM tblTestScores T1
WHERE T1.Score = (
SELECT MAX(T2.Score)
FROM tblTestScores T2
WHERE T1.LastName = T2.LastName
AND T1.FirstName = T2.FirstName
);

Barely worth posting, I know, but I find this kind of subquery
approach easier to integrate into a more complex query than the GROUP
BY approach.

Jamie.

--
 
Back
Top