Rank - Rankings - A Ranked List

[Commish]

Is it possible within Access to create a ranked list of values?
There's no "RANK" function like there is in Excel...

I have a query where I can generate a sum of things by category. For
example, ProjectedHomeRuns by Team.

But, if I wanted to use that to create a set of ProjectedStandings,
what would be the Access way to do that?

Also note, that I would eventually add multiple columns to this, so,
my solution eventually needs to sum the ranks of the categories to
create the Standings. If it was only 1 category, that'd be easy....

 

[Commish]

Is it possible within Access to create a ranked list of values?
There's no "RANK" function like there is in Excel...

I have a query where I can generate a sum of things by category. For
example, ProjectedHomeRuns by Team.

But, if I wanted to use that to create a set of ProjectedStandings,
what would be the Access way to do that?

Also note, that I would eventually add multiple columns to this, so,
my solution eventually needs to sum the ranks of the categories to
create the Standings. If it was only 1 category, that'd be easy....

Excellent - I found how to do it:

SELECT s.HCRLTeam,
(SELECT COUNT(*) FROM [qry12_ProjStatsByTeam] WHERE SumofHR >=
s.SumofHR) AS RankHR,
(SELECT COUNT(*) FROM [qry12_ProjStatsByTeam] WHERE SumofRP >=
s.SumofRP) AS RankRP,
(SELECT COUNT(*) FROM [qry12_ProjStatsByTeam] WHERE SumofNetSB >=
s.SumofNetSB) AS RankSB,
(SELECT COUNT(*) FROM [qry12_ProjStatsByTeam] WHERE SumofTB >=
s.SumofTB) AS RankTB,
(SELECT COUNT(*) FROM [qry12_ProjStatsByTeam] WHERE TeamOBP >=
s.TeamOBP) AS RankOBP,
s.SumOfRP, s.SumofHR, s.SumofNetSB, s.SumofTB, s.TeamOBP
FROM qry12_ProjStatsByTeam AS s;

This works really well. One thing that I'd like to change is this: If
you have the most "SumOfHR" - Access gives you a rank of 1 out of 13.
Can I get Access to give the highest a value of 13 (or however many
values are in the list?

 

[John Spencer]

Try changing the comparison from >= to <=.

If there are ties you might want to make a small change to your sub-queries
and drop the = from the comparison operator, then Add 1 to the result.

1 + (SELECT COUNT(*) FROM [qry12_ProjStatsByTeam] WHERE SumofRP >
s.SumofRP) AS RankRP

The difference is if you had a 3 way tie for 1st place, with your method you
would see
3,3,3,4,5,6,7,8 ... returned. With the modification you would see
1,1,1,4,5,6,7,8 ... returned.


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

Is it possible within Access to create a ranked list of values?
There's no "RANK" function like there is in Excel...

SNIP
Excellent - I found how to do it:

SELECT s.HCRLTeam,
(SELECT COUNT(*) FROM [qry12_ProjStatsByTeam] WHERE SumofHR>=
s.SumofHR) AS RankHR,
(SELECT COUNT(*) FROM [qry12_ProjStatsByTeam] WHERE SumofRP>=
s.SumofRP) AS RankRP,

SNIP

One thing that I'd like to change is this: If

 

[a a r o n . k e m p f]

Access (Jet) doesn't get new features.. but I use RANK / ROWNUMBER in
Access Data Projects ALL the time