Access Rank Function

C

Cip

Need a function that calculates the rank on a daily basis for the following
data

Data stored in table named "MR Daily Stats

need to produce
Date of Work Analysts Name Total Pts Rank
8/15/2005 Bimper,Debra -115 2
8/15/2005 Elias,Erica 44 1
8/15/2005 Frazier,Edith -353 3
8/15/2005 Malone,Tia -359
4
8/12/2005 Kello,Carol -38
6
8/12/2005 Strom,Greg 7
5
8/12/2005 Young,April 102
2
8/12/2005 Edie,Kathy 120 1
8/12/2005 Lang,Cathryb 72 4
8/12/2005 Stinson,Diana 88 3
8/12/2005 Bimper,Debra -116 7
8/12/2005 Claiborne,Cassandra -798 8

Thanks Cip
 
D

Dale Fye

Cip,

Assuming your original table contains
[Date of Work], [Analysts Name], [Points]

I think I would start by creating and saving the following query
(qry_Daily_Point_Totals)

Now, create a second query:

SELECT [Date of Work], [Analysts Name], [Total Points], Rank
From (
SELECT [Date of Work], [Analysts Name], [Total Points],
(SELECT Count([Total Points])
FROM qry_Daily_Point_Totals
WHERE [Date of Work] = T1.[Date of Work]
AND [Total Points] >= T1.[Total Points]) as Rank
FROM qry_Daily_Point_Totals as T1) as Daily_Points_w_Rank
ORDER BY [Date of Work], [Rank]



HTH
Dale
 
M

Michel Walsh

Hi,


SELECT a.DateOfWork,
a.AnalystName,
LAST(a.TotalPoints),
1+COUNT(b.TotalPoints) As rank

FROM myTable As a LEFT JOIN myTable AS b
ON a.DateOfWork=b.DateOfWork
AND a.TotalPoints < b.TotalPoints

GROUP BY a.DateOfWork, a.AnalystName




In case of equality, the lower mark is used. If the points are 100 98 98
93, the ranks are 1 2 2 4




Hoping it may help,
Vanderghast, Access MVP
 
Top