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

L

L34

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.
 
D

Duane Hookom

SELECT LastName, FirstName, Max(Score) as TopScore
FROM tblTestScores
GROUP BY LastName, FirstName;
 
P

PC Datasheet

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.
 
J

Jamie Collins

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.

--
 
Top