Help with SubQuery

[GACat]

Good day:

I have a subquery that I have created to try and get the top 5 scores for a team.

Each team could have 7 members and they all could have the same score (for example 25).

I developed this code following principles from this forum.

SELECT tblParticipant.Division, tblParticipant.TeamName, tblParticipant.County, tblParticipant.Team, tblParticipant.Score, tblParticipant.TO20, tblParticipant.TO21, tblParticipant.TO22, tblParticipant.TO23, tblParticipant.TO24, tblParticipant.TO25, tblParticipant.ShooterID, tblParticipant.FName, tblParticipant.LName
FROM tblParticipant
WHERE tblParticipant.Score IN
(SELECT TOP 5 Score
FROM tblParticipant AS Dupe
Where Dupe.TeamName = tblParticipant.TeamName
ORDER BY Dupe.Score DESC)
ORDER BY tblParticipant.Division, tblParticipant.TeamName, tblParticipant.Score DESC , tblParticipant.TO20 DESC , tblParticipant.TO21 DESC , tblParticipant.TO22 DESC , tblParticipant.TO23 DESC , tblParticipant.TO24 DESC , tblParticipant.TO25 DESC;

Any ideas why it won't give me just the top 5 from each team?

Thanks

 

[MSAccessRookie]

Good day:

I have a subquery that I have created to try and get the top 5 scores for a team.

Each team could have 7 members and they all could have the same score (for example 25).

I developed this code following principles from this forum.

SELECT tblParticipant.Division, tblParticipant.TeamName, tblParticipant.County, tblParticipant.Team, tblParticipant.Score, tblParticipant.TO20, tblParticipant.TO21, tblParticipant.TO22, tblParticipant.TO23, tblParticipant.TO24, tblParticipant.TO25, tblParticipant.ShooterID, tblParticipant.FName, tblParticipant.LName
FROM tblParticipant
WHERE tblParticipant.Score IN
(SELECT TOP 5 Score
FROM tblParticipant AS Dupe
Where Dupe.TeamName = tblParticipant.TeamName
ORDER BY Dupe.Score DESC)
ORDER BY tblParticipant.Division, tblParticipant.TeamName, tblParticipant.Score DESC , tblParticipant.TO20 DESC , tblParticipant.TO21 DESC , tblParticipant.TO22 DESC , tblParticipant.TO23 DESC , tblParticipant.TO24 DESC , tblParticipant.TO25 DESC;

Any ideas why it won't give me just the top 5 from each team?

Thanks

You might want to look into creating GROUPs. As it stands now, all returned records are evaluated at the same time, and you are probably seeing only the top five of all scores. Adding a GROUP BY statement that separates the teams, might bring you a little closer to what you are looking for.

 

[GACat]

I hav edone some tweaking on the coding and tried to add Groups for the TeamName but I keep getting an error that the "query is too complex"

This is what I have now - but I am still not getting what I want - the list of particpants on each team that have the top 5 scores (from the possible 7)

SELECT tblParticipant.Division, tblParticipant.TeamName, tblParticipant.County, tblParticipant.Team, tblParticipant.Score, tblParticipant.ShooterID, tblParticipant.FName, tblParticipant.LName
FROM tblParticipant
WHERE (((tblParticipant.Score) In (SELECT TOP 5 Score
FROM tblParticipant AS Dupe
WHERE Dupe.TeamName=tblParticipant.TeamName
ORDER BY tblParticipant.Score DESC, tblParticipant.ShooterID)))
ORDER BY tblParticipant.Division, tblParticipant.TeamName, tblParticipant.Score DESC;

I have tried several angles - but am not having any luck.

Any help is greatly appreciated!