ranking players in a competition

[JOSELUIS]

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.

 

[KenSheridan via AccessMonster.com]

Replied to other identical post.

Ken Sheridan
Stafford, England

 

[John Spencer]

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 via AccessMonster.com]

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?

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.

 

[John Spencer]

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
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?

 

[JOSELUIS via AccessMonster.com]

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.

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?