From: KenSheridan via on
On second thoughts, that wouldn't work either. The only way I can think of
to distinguish between the players with the same marks and maintain the
ranking by mark otherwise, is to introduce a second subquery:

SELECT Team, PlayerID, PlayerName, Mark,
FROM YourFirstQuery AS Q3
WHERE Q3.Team = Q1.Team
AND Q3.Mark = Q1.Mark
AND Q3.PlayerID > Q1.PlayerID)
FROM YourFirstQuery AS Q2
WHERE Q2.Team = Q1.Team
AND Q2.Mark > Q1.Mark)
AS Rank
FROM YourFirstQuery AS Q1
ORDER BY Team, Mark DESC, PlayerID DESC;

There may be a simpler solution, but if so, it escapes me.

Ken Sheridan
Stafford, England

Message posted via

From: JOSELUIS via on
That´s wonderfull, Thanks John for you help. You`re right I don´t need to
rank the players that is the best solution as you told me.I´m very grateful
to you and also to Ken Sheridan who answer all my doubts and made me to
save a lot of time.

John Spencer wrote:
>My error, I forgot the GROUP BY clause.
>SELECT Team, Sum(Mark) as Top3Sum
>FROM [qryplayers] as Main
>WHERE PlayerID in
> (SELECT Top 3 PlayerID
> FROM [qryplayers] as Temp
> WHERE Temp.Team = Main.Team
> ORDER BY Mark Desc, PlayerID)
>John Spencer
>Access MVP 2002-2005, 2007-2010
>The Hilltop Institute
>University of Maryland Baltimore County
>> because I need some calculations to get the players mark I stored this data
>> in qryplayers so I wrote the following SQL based on yours
>[quoted text clipped - 8 lines]
>> it be because mark is in a query instead of a table or maybe there would be
>> another posibility?

Message posted via