Wrong Info on Group Query

[Leona Leal Educator]

I am at a loss why my Group Query is not Working.

I want to group by State. However when I want the student with the highest
test score I get the student som place in the middle.

Very simple code but it is not working!

1st Column

State
TableName
Group By
Ascending

2nd Column

TestScore
TableName
Last
Ascending

3rd Column

NameOfStudent
TableName
Last


The Highest Test Score for Alaska is 2330

However I am getting the student with a test score of 1240

What am I not understanding. I am just coming over from IBM DB2 so I am new
to Microsoft.

What am I misunderstanding?

Thanks in Advance

Granny Leona

 

[Golfinray]

Try just sorting the 2nd column.

 

[Leona Leal Educator]

No Go, same results.

Thanks for the idea.

Try just sorting the 2nd column.

 

[John Spencer]

What you are misunderstanding is that LAST for all practical purposes returns
a random record from the group. It is the LAST record accessed within the
group and not the record with the highest score or the latest date or whatever.

Assuming that your field and table names follow the naming conventions
(Letters, Numbers, and underscore characters only) you can do this in one
query. The SQL of the query would look like this.

SELECT State, TestScore, NameOfStudent
FROM YourTable as A
INNER JOIN
(SELECT State, Max(TestScore) as HighScore
FROM YourTable
GROUP BY State) as B
ON A.State = B.State
AND A.TestScore = B.HighScore

As a general rule, you can do this with a series of queries.
Build a query that returns the state and the high score.
Save the query.

Now, build another query based on the saved query and the table.
Join the table and saved query on the State fields and the Score fields
Select the fields you wish to see displayed.

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