Solved

# PercentRanking in Access2002

Posted on 2009-02-20
218 Views
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;
``````
0
Question by:SusanSSS

LVL 92

Expert Comment

Hello SusanSSS,

Please explain what you are trying to do.

Regards,

Patrick
0

Author Comment

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

LVL 92

Expert Comment

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

LVL 92

Accepted Solution

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

Author Closing Comment

Thank you!! This worked... I have tried several way and this was fast and easy.
Susan
0

## Featured Post

### Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…