ranking players in a competition
in [Queries]
|
From: JOSELUIS on 10 Feb 2010 18:04 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: KenSheridan via AccessMonster.com on 10 Feb 2010 19:29 Jose Luis: The following query, based on your existing query, 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) AS Rank FROM YourFirstQuery AS Q1 ORDER BY Team, Mark DESC, PlayerID, PlayerName; You can then base another query on this second query to give you the team marks: SELECT Team, SUM(Mark) AS TeamMark FROM YourSecondQuery WHERE Rank <=3 GROUP BY Team; In each case change the query and/or field names to your real ones, but you don't need to change the Q1 or Q2 aliases. Bear in mind that you could get more than 3 players per team ranked at 3 or above if 2 or more tie for third place, 3 or more tie for second place or 4 or more tie for first place. No need to apologise for your English; its far better than my meagre Spanish! But please don't post in all capital letters; it sounds like you are shouting and is not considered good 'netiquette'. Ken Sheridan Stafford, England 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) IF RANKING= 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: KenSheridan via AccessMonster.com on 10 Feb 2010 19:31 Replied to other identical post. Ken Sheridan Stafford, England 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1
From: JOSELUIS via AccessMonster.com on 11 Feb 2010 17:58 KenSheridan wrote: >Jose Luis: > >The following query, based on your existing query, 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) AS Rank >FROM YourFirstQuery AS Q1 >ORDER BY Team, Mark DESC, PlayerID, PlayerName; > >You can then base another query on this second query to give you the team >marks: > >SELECT Team, SUM(Mark) AS TeamMark >FROM YourSecondQuery >WHERE Rank <=3 >GROUP BY Team; > >In each case change the query and/or field names to your real ones, but you >don't need to change the Q1 or Q2 aliases. > >Bear in mind that you could get more than 3 players per team ranked at 3 or >above if 2 or more tie for third place, 3 or more tie for second place or 4 >or more tie for first place. > >No need to apologise for your English; its far better than my meagre Spanish! >But please don't post in all capital letters; it sounds like you are shouting >and is not considered good 'netiquette'. > >Ken Sheridan >Stafford, England > >>As You see I´m not writing in capital letters anymore and thank you for your help but I´m afraid I need a bit more of your help. I create the first query based on my query and it works but not totally because the field rank appears in all players of each team as 1.Could yould tell me any other way or idea? Best regards Jose Luis, Leon,Spain. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1
From: KenSheridan via AccessMonster.com on 11 Feb 2010 18:49 Jose Luis: It shouldn't do that. Please post the SQL and name of your original query. Ken Sheridan Stafford, England JOSELUIS wrote: >>Jose Luis: >> >[quoted text clipped - 31 lines] >> >>>As You see I´m not writing in capital letters anymore and thank you for your help but I´m afraid I need a bit more of your help. I create the first query based on my query and it works but not totally because the field rank appears in all players of each team as 1.Could yould tell me any other way or idea? >Best regards >Jose Luis, >Leon,Spain. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1
|
Next
|
Last
Pages: 1 2 3 Prev: Multiple Pivot Charts to display in one page Next: ranking players in a competition |