Can a Group Query Do What I need to do?

[Len]

I have a table. I need to select the student with the highest test score
from each school.

I am selecting the State for Test purposes!

In the Query below, I get the right test score, but I cannot get the correct
student that the test score matches.

How can this be accomplished?

SELECT FederalClass.ST, Max(FederalClass.TotPotTest) AS MaxOfTotPotTest,
First(FederalClass.StudId) AS FirstOfStudId, Last(FederalClass.ENRL) AS
LastOfENRL, Last("7-A") AS Expr1
FROM FederalClass
GROUP BY FederalClass.ST
ORDER BY FederalClass.ST, Max(FederalClass.TotPotTest);

Do I need to do this in VB?

Thanks in Advance

Len

 

[John Spencer]

Try using a sub-query in the FROM clause to identify the St and MaxScore for
each ST. Join your table to the ST and MaxScore

SELECT F.St, F.TotPotTest, F.StudID, F.Enrl, "7-A" as Expr1
FROM FederalClass as F INNER JOIN
(SELECT ST, Max(TotPotTest) as MaxScore
FROM FederalClass
GROUP BY ST) as F2
ON F.St = F2.ST
and F.TotPotTest = F2.MaxScore

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