Prev: Multiple Pivot Charts to display in one page
Next: ranking players in a competition
From: John Spencer on 12 Feb 2010 07:28
You could use a correlated top 3 query to get the Sum. That way you don't
need to rank the players. The following query gets the sum of the top 3 marks
per team and in the case of any ties uses the player id to ensure that you are
only getting 3 scores.

SELECT Team, Sum(Mark) as Top3Sum
FROM [SomeTable] as Main
WHERE PlayerID in
(SELECT Top 3 PlayerID
FROM [SomeTable] as Temp
WHERE Temp.Team = Main.Team
ORDER BY Mark Desc, PlayerID)

IF you still need to rank the players you can use several techniques. The
simplest (and probably slowest) is to use the DCount function. My guess is
that Team is a text field and Mark is a number field.

SELECT Team, PlayerID, PlayerName, Mark
, 1 + DCount("*","[SomeTable]","Team=""" & [Team] & """ AND Mark <" & [Mark])
as Rank
FROM [SomeTable]


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

JOSELUIS wrote:
> HI MY NAME IS JOSE LUIS FROM SPAIN I´M A ROCKIE IN ACCESS.I CREATE A ACCESS
> DATABASE FOR A SPORT COMPETITION. IN A QUERY I CREATE :
> PLAYERID/PLAYERNAME/TEAM/MARK BUT I NEED TO RANK ALL THE PLAYERS IN EACH
> TEAM.
> WHY? BECAUSE ONLY THE THREE BEST PLAYERS IN EACH TEAM ARE TAKING INTO ACCOUNT
> IN THE TEAM CLASIFICATION THEREFORE THE PLAYER WHOSE MARK IS HIGHER WILL BE
> 1º AND SO ON UPTO 3º AND TEAMMARK= SUM(MARK ONLY 1º,2º,3º)
> SORRY FOR MY ENGLISH AND THANK YOU IN ANTICIPATION.
>
From: JOSELUIS via AccessMonster.com on 12 Feb 2010 17:52
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
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)
but an error message is displayed " Team is not an agregate function"Could
it be because mark is in a query instead of a table or maybe there would be
another posibility?
John Spencer wrote:
>You could use a correlated top 3 query to get the Sum. That way you don't
>need to rank the players. The following query gets the sum of the top 3 marks
>per team and in the case of any ties uses the player id to ensure that you are
>only getting 3 scores.
>
>SELECT Team, Sum(Mark) as Top3Sum
>FROM [SomeTable] as Main
>WHERE PlayerID in
> (SELECT Top 3 PlayerID
> FROM [SomeTable] as Temp
> WHERE Temp.Team = Main.Team
> ORDER BY Mark Desc, PlayerID)
>
>IF you still need to rank the players you can use several techniques. The
>simplest (and probably slowest) is to use the DCount function. My guess is
>that Team is a text field and Mark is a number field.
>
>SELECT Team, PlayerID, PlayerName, Mark
>, 1 + DCount("*","[SomeTable]","Team=""" & [Team] & """ AND Mark <" & [Mark])
>as Rank
>FROM [SomeTable]
>
>John Spencer
>Access MVP 2002-2005, 2007-2010
>The Hilltop Institute
>University of Maryland Baltimore County
>
>> HI MY NAME IS JOSE LUIS FROM SPAIN I´M A ROCKIE IN ACCESS.I CREATE A ACCESS
>> DATABASE FOR A SPORT COMPETITION. IN A QUERY I CREATE :
>[quoted text clipped - 4 lines]
>> 1º AND SO ON UPTO 3º AND TEAMMARK= SUM(MARK ONLY 1º,2º,3º)
>> SORRY FOR MY ENGLISH AND THANK YOU IN ANTICIPATION.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1

From: JOSELUIS via AccessMonster.com on 12 Feb 2010 18:10
I´m really sorry I made a mistake in the SQL but You´re right it works
perfectly but you get a good point now it can be that two or more players get
hte same ranking.Any ideas?

KenSheridan wrote:
>Jose Luis:
>
>It shouldn't do that. Please post the SQL and name of your original query.
>
>Ken Sheridan
>Stafford, England
>
>>>Jose Luis:
>>>
>[quoted text clipped - 4 lines]
>>Jose Luis,
>>Leon,Spain.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1

From: John Spencer on 12 Feb 2010 19:26
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

JOSELUIS via AccessMonster.com wrote:
> 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
> 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)
> but an error message is displayed " Team is not an agregate function"Could
> it be because mark is in a query instead of a table or maybe there would be
> another posibility?
From: KenSheridan via AccessMonster.com on 13 Feb 2010 07:47
Strictly speaking two players with the same mark are the same rank of course,
but you can give one an artificially higher rank than the other provided that
there is some way of uniquely distinguishing between them, which I assume
PlayerID does. So using this also in the subquery should do it:

SELECT Team, PlayerID, PlayerName, Mark,
(SELECT COUNT(*)+1
FROM YourFirstQuery AS Q2
WHERE Q2.Team = Q1.Team
AND Q2.Mark > Q1.Mark
AND Q2.PlayerID > Q1.PlayerID) AS Rank
FROM YourFirstQuery AS Q1
ORDER BY Team, Mark DESC, PlayerID, PlayerName;

Ken Sheridan
Stafford, England

JOSELUIS wrote:
>I´m really sorry I made a mistake in the SQL but You´re right it works
>perfectly but you get a good point now it can be that two or more players get
>hte same ranking.Any ideas?
>
>>Jose Luis:
>>
>[quoted text clipped - 8 lines]
>>>Jose Luis,
>>>Leon,Spain.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1

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