Mulitple Categories in a query

[Paid The Umpire]

I have a table which contains ten different categories: A through J.

With the following of field...

Category, Score, Player1, Player2, MatchID

I'm trying to find a nice query that will give me the highest score for
each category. As well as show the players and matchID where involved.

Any ideas?

 

[Allen Browne]

See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

The article should give you several ways to generate this query.

 

[John Spencer]

SELECT *
FROM [Your Table] as A
WHERE A.Score =
(SELECT MAX(Score)
FROM [Your Table] as Tmp
WHERE Tmp.Category = A.Category)

You can also do this with two queries.

Query one is a totals query that gets the Max score for each category. Save
it as qMaxCategory

Query two uses query one and the table. You join Category to Category and
Score to MaxScore

 

[Paid The Umpire]

That is prefect.

I'm now trying to find a query like the above mentioned.

Except that I want to get the highest in each category, for a second
field "Grade".

So that I can get the highest score in each category for each grade.
Any further ideas?

SELECT *
FROM [Your Table] as A
WHERE A.Score =
(SELECT MAX(Score)
FROM [Your Table] as Tmp
WHERE Tmp.Category = A.Category)

You can also do this with two queries.

Query one is a totals query that gets the Max score for each category. Save
it as qMaxCategory

Query two uses query one and the table. You join Category to Category and
Score to MaxScore

I have a table which contains ten different categories: A through J.

With the following of field...

Category, Score, Player1, Player2, MatchID

I'm trying to find a nice query that will give me the highest score for
each category. As well as show the players and matchID where involved.

Any ideas?