trouble with min/max queries

[CarlyS]

I have a MainQuery with the following fields:
ID-(the patient's ID)
Score
ItemID

I am trying to get the highest score for each patient and be able to identify which item was their high score, so basically I want to end up with the following query results:

ID
(Patient'sMax)Score
ItemID

To do this I was trying the following two queries:
Query1:
SELECT Max([MainQuery].[Score]) AS MaxOfScore, [MainQuery].[ID]
FROM MainQuery
GROUP BY [MainQuery].[ID];


Query2:
SELECT [MainQuery].[Score], [MainQuery].[ID], [MainQuery].[ItemID]
FROM MainQuery INNER JOIN Query1 ON ([MainQuery].[ID]=[Query1].[ID]) AND ([MainQuery].[Score]=[Query1].[MaxOfScore]);

What I find is that Query1 works and Query2 returns more than just the patients' max scores. Can anyone see the problem in my second query?

Thanks!
Carly

 

[Jon K]

The queries are correct. I guess there are ties in the max score for certain IDs.


To resolve the problem of ties, you can build a Totals query off Query2 to select either the First, Last, Max or Min value of the ItemIDs e.g.

Query3:
SELECT Query2.Score, Query2.ID, First(Query2.ItemID) AS FirstOfItemID
FROM Query2
GROUP BY Query2.Score, Query2.ID;
.

 

[CarlyS]

that was it!

Fabulous--Thank you so much!