Inner join query not quite right

[coach32]

I have two queries; the first is:
[code]SELECT Max(tblWinnersOnlyAllAges.P1) AS MaxFor, tblWinnersOnlyAllAges.Team, tblWinnersOnlyAllAges.Age


FROM tblWinnersOnlyAllAges
GROUP BY tblWinnersOnlyAllAges.Team, tblWinnersOnlyAllAges.Age
ORDER BY tblWinnersOnlyAllAges.Team, tblWinnersOnlyAllAges.Age;[code]
The results from this query are exactly what I want to start with.
eg: Berwick U10 162
Berwick U11 175
Berwick U12 180 etc. This is done for all Teams. After this query I need to show what is on the whole row of each of the Maximum records, so I ran the following query:
[code]SELECT tblWinnersOnlyAllAges.*, tblWinnersOnlyAllAges.Team, tblWinnersOnlyAllAges.Age, tblWinnersOnlyAllAges.P1
FROM tblWinnersOnlyAllAges INNER JOIN qryHSFAll ON tblWinnersOnlyAllAges.Team = qryHSFAll.Team
WHERE (((tblWinnersOnlyAllAges.P1)=[qryHSFAll]![MaxFor]));[code]
This almost works, except it show an odd record every now and then and I can't understand why. eg: Berwick 26 10 162 U10 1988 is the Maximum value for that Team in that Age but the results may also show Berwick 22 12 144 U10 1991. Could anyone please shed some understanding on what I am doing wrong with my second query. Thank-you for taking the time to look at this long winded post.

[June7]

End code tag should be /code.

I would have to examine source data and test. If you want to provide, follow instructions at bottom of my post.

[coach32]

Thank you June7 for taking a look. I have attached an abbreviated version. When you run the second query (qryHSFAllA), sort the 'Team' names, you will see that, for example Beaconsfield have two lines for Under 13s with different values. I only wanted the Maximum value for each age which shows in the first query (qryHSFAll).

[recyan]

Till June's back,

Try running below query & see what happens :

SELECT 
    qryHSFAll.Team, 
    qryHSFAll.Age, 
    qryHSFAll.MaxFor, 
    tblWinnersOnlyAllAges.*
FROM 
    qryHSFAll 
    INNER JOIN 
    tblWinnersOnlyAllAges 
    ON 
    (qryHSFAll.Team = tblWinnersOnlyAllAges.Team) 
    AND 
    (qryHSFAll.Age = tblWinnersOnlyAllAges.Age) 
    AND 
    (qryHSFAll.MaxFor = tblWinnersOnlyAllAges.P1)
ORDER BY 
    qryHSFAll.Team, qryHSFAll.Age, qryHSFAll.MaxFor;

This query will give you 129 records versus 125 records of qryHSFAll.
If you now look at your data in table tblWinnersOnlyAllAges,
you will find
1) Beaconsfield U14 has 2 records with max value 195.
2) Berwick U13 has 2 records with max value 237.
3) Berwick Springs/Beaconsfield U14 has 2 records with max value 91.
4) Dandenong Saints (St.Mary's Church) U15 has 2 records with max value 193.

This, I believe is the first thing that needs to be looked in to ( either the logic of the grouping in qryHSFAll or the data in tblWinnersOnlyAllAges ).

Thanks

[June7]

When I open qryHSFAllA I get an input parameter prompt for Query1.tblWinnersOnlyAllAges.Team. Very odd because nothing in the sql statement references Query1. I just OK past the prompt and the query opens with 181 records. I remove the Team and Age fields from the query grid because they are already captured by the field wildcard (*). Now the prompt no longer pops and I still get the 181 records.

However, there are only 2 Beaconsfield records, for U10 and U13. Not getting the issue you describe with that query.

recyan has exposed what I suspected but had not yet tracked down - that more than one record matches to the Max value.

[coach32]

Thank-you to recyan and June7. I ran the query and that's the results I want. I should say that there will be the odd time that a Team does have a multiple Max value.

[recyan]

Glad you got things working.

Thanks