Query returning strange results when using 'MAX'

[coach32]

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 given advise to have two select queries as below:


Code:

SELECT Max(AusFootBall.P) AS MaxOfP, AusFootBall.Rd


FROM AusFootBall
GROUP BY AusFootBall.Rd;

And then run the second query:


Code:

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

I need to see the whole row that the Maximum score applies to, hence the second query.
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.

[hertfordkc]

understanding of Aussie Rules football.
I may have a go in the morning if no one else has solved your problem...and the Scotch is gone.

[coach32]

Thank-you. That would be great.

[June7]

It should work but I am baffled by the Year field. Is this multi-year data? Does each year have Rd 1, 2, 3, etc? Query appears to be returning records from multiple years although the GROUP query is finding the Max disregarding the year. Is this what you want?

Can you provide raw data? A spreadsheet would be adequate.

[coach32]

I have attached a sample of the data.
Yes, each year does have Rounds 1 through to 14.
The scores cover years 1963 thorugh to 2011.
The end result I would like is for the query to show the Maximum score from each Division for each Round.

[June7]

The Max query is not what you posted. It has Dn as filter criteria (1 Or 2 Or 3) but with GROUP BY so the grouping includes the Dn field and returned 6 Max records for the combined fields of Rd and Dn. Change Dn to WHERE and only 2 Max records will return. Now Query2 returns two records instead of all 9.

This returns the Max record of all years combined.

Works with the limited sample provided.