ranking

[Guest]

I have read the prior post on ranking but still not clear on it.

here is my query

SELECT Results.[CHARTER NO], Results.NAME, [Results]![TOT FID & RLTD ASSETS
- TOT FID ASSETS $]/[Results]![TOT ASSETS $ (BANK) (2004Q2)] AS [Fid Assets %
of Bank Assets], [Results]![FID ACTVS INC $]/[Results]![ADJ OP INC-TE $] AS
[Fid Inc % of Adj Op Inc], [Results]![FID & RLTD SVCS INC - CUSTODY & SKPG
$]/[Results]![FID ACTVS INC $] AS [Cust and Safekeeping $ of Fid Inc]
FROM Results
WHERE ((([Results]![FID & RLTD SVCS INC - CUSTODY & SKPG $]/[Results]![FID
ACTVS INC $])<0.08));

What I want to do is. rather than have the last three fields display as a
percent I would like to to be ranked (1-40 or however many record there are)
Ties are Ok, but will 1% actually .005698 be a tie with 1% that is .00678?
both display as a 1% but are actually different. No tie would be good in
this case

 

[Guest]

follow-up
the rank can be in a new field while keeping the percent in its existing field

 

[Tom Ellison]

Dear Tim:

Because there is a bit going on in calculating the 3 values, I
recommend we base a new query on the existing one. However, I'd like
to shorten the column names you calculate to make it easier. So, I'm
calling the 3 calculated columns C1, C2, and C3. You may retain your
long names and replace these if you wish.

I am assuming then that you save this as MyQuery:

SELECT [CHARTER NO], NAME,
[TOT FID & RLTD ASSETS - TOT FID ASSETS $] /
[TOT ASSETS $ (BANK) (2004Q2)] AS C1,
[FID ACTVS INC $] / [ADJ OP INC-TE $] AS C2,
[FID & RLTD SVCS INC - CUSTODY & SKPG $] / [FID ACTVS INC $]
AS C3
FROM Results
WHERE [FID & RLTD SVCS INC - CUSTODY & SKPG $] / [FID ACTVS INC $] <
0.08;

The query with rankings would then be:

SELECT [CHARTER NO], NAME,
(SELECT COUNT(*) + 1 FROM Q1
WHERE Q1.C1 > Q.C1) AS Rank1,
(SELECT COUNT(*) + 1 FROM Q1
WHERE Q1.C2 > Q.C2) AS Rank2,
(SELECT COUNT(*) + 1 FROM Q1
WHERE Q1.C3 > Q.C3) AS Rank3
FROM MyQuery Q

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