Prev: Multiple Pivot Charts to display in one page
Next: ranking players in a competition
From: KenSheridan via AccessMonster.com on 13 Feb 2010 13:28
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,
(SELECT COUNT(*)+1+
(SELECT COUNT(*)
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 AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1

From: JOSELUIS via AccessMonster.com on 13 Feb 2010 15:23
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)
>GROUP BY Team
>
>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 AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1

First  |  Prev  | 
Pages: 1 2 3
Prev: Multiple Pivot Charts to display in one page
Next: ranking players in a competition