Hi Colin and Arvil
I managed to get the result I wanted by using a combination two queries.
The first qry3orMoreEvents reads the table ShootScores and returns members who have competed in 3 or more events
Code:
SELECT Members.[Last Name], Members.[First Name], ShootScores.ScoresID, ShootScores.DateOfShoot, ShootScores.Event, ShootScores.Score, ShootScores.XScore
FROM Members INNER JOIN ShootScores ON Members.[Member ID] = ShootScores.[Member ID]
WHERE (((ShootScores.[Member ID]) In (SELECT [Member ID] FROM [ShootScores] As Tmp GROUP BY [Member ID] HAVING Count(*)>=3 )))
ORDER BY ShootScores.[Member ID];
I then incorporated this query as part of a second query Top3Scores which returns the highest 3 scores for each competitor who cmpeted in 3 or more events
Code:
SELECT Members.[Last Name], Members.[First Name], ShootScores.Score, [Event Type].[Event Type], ShootScores.DateOfShoot
FROM ((Members INNER JOIN ShootScores ON Members.[Member ID] = ShootScores.[Member ID]) INNER JOIN [Event Type] ON ShootScores.Event = [Event Type].ID) INNER JOIN qry3orMoreEvents ON ShootScores.ScoresID = qry3orMoreEvents.ScoresID
GROUP BY Members.[Last Name], Members.[First Name], ShootScores.Score, [Event Type].[Event Type], ShootScores.DateOfShoot, ShootScores.[Member ID], ShootScores.ScoresID
HAVING (((ShootScores.ScoresID) In (SELECT TOP 3 ScoresID FROM ShootScores AS Dupe
WHERE Dupe.[Member ID] = ShootScores.[Member ID]
ORDER By Dupe.Score DESC, Dupe.ScoresID DESC)))
ORDER BY Members.[Last Name], Members.[First Name], ShootScores.[Member ID];
On the dataset I am using this all seems to work nicely and returns the correct values.
Now, ever the thinker, I'm wondering how I can have the query prompt me to enter the number of qualifying events, rather than hard coding the value 3. This means that if there is a change in the qualifying rules for the association, they can be catered for instead of having to modify the queries.
Arvil, I like your idea and will have a play with it. Nice that it's all in one and may actually make the above question easier to answer.