PercentRanking in Access2002

Is there a way to to calcualte PercentRank in Access? Creating productivity report for team and need to rank preformace daily ... have tried and always fails.... Help Please!
Public Function myPercentRank(t As String, f As String, d As Double) As Double
 
    Dim rs As DAO.Recordset
    Dim v As Variant
    
    Set rs = CurrentDb.OpenRecordset("SELECT " & f & " FROM " & t & ";")
    v = rs.GetRows
    
    myPercentRank = Excel.WorksheetFunction.PercentRank(v, d)
    rs.Close
    Set rs = Nothing
End Function
 
SELECT RankRepo.Name, RankRepo.AssgnHr, myPercentRank("RankRepo","AssgnHr",[AssgnHr]) AS Expr1
FROM RankRepo;

Open in new window

SusanSSSAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
You really do not even need a UDF...



SELECT [Name], AssgnHr, DCount("*", "RankRepo", "[AssgnHr] < " & [AssgnHr]) / IIf(DCount("*", "RankRepo") > 1, DCount("*", "RankRepo") - 1, 1) AS PercRank
FROM RankRepo;
0
 
Patrick MatthewsCommented:
Hello SusanSSS,

Please explain what you are trying to do.

Regards,

Patrick
0
 
SusanSSSAuthor Commented:
Hello!

PERCENTRANK is a function in Excel, I need to find away to do it in Access

Calls      Calls PR
14.13      71.42%
12.30      50.00%
12.08      42.85%
15.52      78.57%
8.13      0.00%
11.33      28.57%
29.43      100.00%
11.12      21.42%
9.88      7.14%
11.45      35.71%
13.33      64.28%
11.11      14.28%
18.12      85.71%
25.17      92.85%
12.59      57.14%

In excel    =PERCENTRANK($S$2:$S$16,S2,4)

0
 
Patrick MatthewsCommented:
This seems to do it:



Public Function myPercentRank(t As String, f As String, d As Double) As Double

    Dim rsBelow As DAO.Recordset
    Dim rsCount As DAO.Recordset
    Dim arr() As Double
   
    Set rsBelow = CurrentDb.OpenRecordset("SELECT Count(*) AS Cnt FROM [" & t & "] " & _
        "WHERE [" & f & "] < " & d)
    Set rsCount = CurrentDb.OpenRecordset("SELECT Count(*) - 1 AS Cnt FROM [" & t & "]")
   
    myPercentRank = rsBelow!cnt / IIf(rsCount!cnt > 0, rsCount!cnt, 1)

    rsCount.Close
    rsBelow.Close
    Set rsCount = Nothing
    Set rsBelow = Nothing

End Function
0
 
SusanSSSAuthor Commented:
Thank you!! This worked... I have tried several way and this was fast and easy.
Susan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.