Removing odd number out

[coach.32]

I have a database that collates the scores from our local Aussie Rules football league. I needed to obtain the maximum score kicked for each particular Round. I was advised from this Forum to have two select queries as below:

[COLOR=black][FONT=Verdana]SELECT Max(AusFootBall.P) AS MaxOfP, AusFootBall.Rd
FROM AusFootBall
GROUP BY AusFootBall.Rd; [/FONT][/COLOR]

And then run the second query:

[COLOR=black][FONT=Verdana]SELECT AusFootBall.*
FROM AusFootBall INNER JOIN QueryAus1_GetMaxByRd 
ON AusFootBall.Rd = QueryAus1_GetMaxByRd.Rd
WHERE (((AusFootBall.P)=[QueryAus1_GetMaxByRd]![MaxOfP])); [/FONT][/COLOR]

This worked exceptionally well except on the odd occasion I would receive data like the following:
Rd Dn Team G B P Team2 Year
1 1 Berwick 15 10 100 Noble Park 1998
1 1 Souths 5 7 37 Wests 1981
1 2 Hallam 12 19 81 Geelong 2010
1 3 Cobram 5 7 37 St.Kilda 2001
I found that if the highest score in Division 3 was 37 for example, then the result for Division 1 would show it's highest score and also a random score that matched the 37 from Division 3. Is there anyway to get rid of the unwanted score(s). It happened for more than one round. Thank you in advance.

 

[JamesMcS]

Could you upload the DB ? I'm struggling to see why this can't be done in the one query, but it is early....

 

[coach.32]

Hi James
I have uploaded a sample as the DB was is very big, but the data contained in the upload is enough. I think the reason for the second query was so that when I found what the Maximum scores were I then needed to list the entire row for each one. The perfect example of what my problem is can be seen when you look at the Round 24 scores. It shows a number of 5.7.37 results in Division 1 & 2 just because Division 3's highest is 5.7.37. Thank you for your time, it is greatly appreciated.