Selecting MAX from my database

[coach.32]

I have a database that contain over 10,000 records from football (Australian Rules) scores. An example of the data is as follows:
Rd Dn Team G B P Team2 Year
1 1 Berwick 15 10 100 Noble Park 1998
1 1 Hallam 12 19 81 Geelong 2010
2 1 Cobram 14 12 96 St.Kilda 2001
2 1 St.Kilda 15 18 108 Essendon 1974
How can I create a query that will show the Highest Points ('P') for each particular 'Rd' and will show the whole row for each of the Max scores. I have tried the SELECT query which works for just one particular Rd and just shows the actual 'P' value. So from the above data I would like the following result from my query.
1 1 Berwick 15 10 100 Noble Park 1998
2 1 St.Kilda 15 18 108 Essendon 1974 being the Maximum scores for those Rd's. Thank you in advance for any help you can offer me.

 

[jdraw]

Use 2 queries.

Create a query named QueryAus1_GetMaxByRd with this sql

SELECT Max(AusFootBall.P) AS MaxOfP, AusFootBall.Rd
FROM AusFootBall
GROUP BY AusFootBall.Rd;

Create a second Query that uses the first, as follows:

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

Good luck.

 

[coach.32]

Thank-you JDraw, that worked a treat. I then decided that I needed to show the Highest 'P' for each round for each of the 3 divisions that we have (1, 2, 3).
When I did this, 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. Hence I have a result as follows:
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
Is there anyway to get rid of the unwanted score(s). It happened for more than one round. Thank you again.