Ranking Inside a Querry

[Alan Balthrop]

Thanks to Garry Miller for his assistance last night with my first set
of questions. I am brand new to Access, so I apoligize if I ask
questions that have been asked before.

In a database of soccer statistics, I have all the statistics from
every season in one table. I wrote a querry ranking the players totals
for a paticular catagory from highest to lowest. I would like to
insert a column to the left of the players name called "Rank" that
assends from 1 to XX (xx=number of players in this catagory) so that
when the querry is returned it looks like this:

Rank PlayerName GP
1. Tatu 557
2. Powers 459
3. Smith 434
4. David 400
4. Jones 400
etc.



Is such a thing possible? If it is, would it be possible for Access to
recognize the same data in a field and have the rank be a tie (in the
example above, two players each playing 400 games would have a rank of
4)?


Thank you in advance for your help and patience with a newcommer to
the program!



Alan Balthrop
Team Historian
Dallas Sidekicks Indoor Soccer Club
(e-mail address removed)

 

[Tom Ellison]

Dear Alan:

SELECT
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.GP > T.GP) AS Rank,
PlayerName, GP
FROM YourTable T
ORDER BY GP

This uses a subquery to count the number of players with a higher GP,
adding one so that when there are 0 players who are better, the rank
is 1 (instead of 0).

The next player would be ranked 6th assuming his GP < 400. 5th place
would be skipped. That's probably what you wanted, right?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Alan Balthrop]

The next player would be ranked 6th assuming his GP < 400. 5th place
would be skipped. That's probably what you wanted, right?

This is exactly what I want. This is how Access 2002 is writing the
SQL for the querry I created currently:

SELECT DISTINCTROW field.Player, Sum(field.GP) AS [Sum Of GP]
FROM field
GROUP BY field.Player
HAVING (((field.Player) Not In ("others","bench")));



Would I add tyour suggestion:

SELECT
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.GP > T.GP) AS Rank,
PlayerName, GP
FROM YourTable T
ORDER BY GP


....to my code or would I replace mine with yours (hopefully that
question makes sense)?

 

[Tom Ellison]

Dear Alan:

It appears your query is creating the [Sum Of GP]. If you want your
rankings based on that sum, then you should adapt the query I gave you
to be based on that query. Substitute the name of the query for
YourTable in the code I sent. Use [Sum Of GP] instead of GP and
Player instead of PlayerName.

It seems odd that you would name a table or query with "field".

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

The next player would be ranked 6th assuming his GP < 400. 5th place
would be skipped. That's probably what you wanted, right?

This is exactly what I want. This is how Access 2002 is writing the
SQL for the querry I created currently:

SELECT DISTINCTROW field.Player, Sum(field.GP) AS [Sum Of GP]
FROM field
GROUP BY field.Player
HAVING (((field.Player) Not In ("others","bench")));



Would I add tyour suggestion:

SELECT
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.GP > T.GP) AS Rank,
PlayerName, GP
FROM YourTable T
ORDER BY GP


...to my code or would I replace mine with yours (hopefully that
question makes sense)?

 

[Alan Balthrop]

Dear Alan:

It appears your query is creating the [Sum Of GP]. If you want your
rankings based on that sum, then you should adapt the query I gave you
to be based on that query. Substitute the name of the query for
YourTable in the code I sent. Use [Sum Of GP] instead of GP and
Player instead of PlayerName.

Wilco.


It seems odd that you would name a table or query with "field".

the name comes from "Field Players" as opposed to another table for
"goalkeepers"