By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,878 Members | 1,382 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,878 IT Pros & Developers. It's quick & easy.

Percentile Query?

P: 76
It's me again.

-->empUid - self explanatory
-->empPerf - ranking from 0 - 100

I'm interested in creating a query that displays the top x% (eg, 20%) of employee performance, based on empPerf. Qry operator will be prompted for value of X%.

I'm not too familiar with stats, but searched through the site and found l was only able to find something regarding Excel.

In advance..............THANKS!
Jan 28 '09 #1
Share this Question
Share on Google+
5 Replies

Expert 5K+
P: 8,692
  1. I wrote a very simple Algorithm that will calculate a Percentile Rank as the proportion of Scores in a Distribution that an Employee's score is greater than or equal to. As an example, if Employee X received a Score (empPerf) of 95, and this Score is greater than or equal to 88% of the other Employee's Scores, then his/her Percentile Rank would be in the 88th Percentile. This code will not win any coding awards, it ain't even pretty, but it do work! (LOL)!
  2. First, a couple of very simple Assumptions:
    1. Table Name is tblEmp
    2. empUID is the Primary Key (Data Type not relevant)
    3. empPerf is a BYTE Data Type, must be >=0 and <=100, and cannot be NULL
  3. Enough already, below please find the SQL and the Function code below:
    Expand|Select|Wrap|Line Numbers
    1. SELECT Employees.EmployeeID, Employees.empPerf, fCalculatePercentile([empPerf]) AS Percentile
    2. FROM Employees
    3. ORDER BY Employees.empPerf DESC;
    Expand|Select|Wrap|Line Numbers
    2. Public Function fCalculatePercentile(bytPerf As Byte) As String
    3. Dim MyDB As DAO.Database
    4. Dim rstPercentile As DAO.Recordset
    5. Dim intGreater As Integer
    6. Dim intLess As Integer
    7. Dim intAllScores As Integer
    8. intAllScores = DCount("*", "Employees")     'Total Number of Scores
    9. Set MyDB = CurrentDb
    10. Set rstPercentile = MyDB.OpenRecordset("Employees", dbOpenForwardOnly)
    11. With rstPercentile
    12.   Do While Not .EOF
    13.     If bytPerf >= ![empPerf] Then
    14.       intGreater = intGreater + 1       'Increment > Counter
    15.     Else
    16.     End If
    17.     .MoveNext
    18.   Loop
    19. End With
    20. rstPercentile.Close
    21. Set rstPercentile = Nothing
    22. fCalculatePercentile = Format$(intGreater / intAllScores, "Percent")
    23. End Function 
Jan 28 '09 #2

Expert 5K+
P: 8,692
Oops, wrong SQL!
Expand|Select|Wrap|Line Numbers
  2. SELECT tblEmp.empUid, tblEmp.empPerf, fCalculatePercentile([empPerf]) AS Percentile 
  3. FROM tblEmp 
  4. ORDER BY tblEmp.empPerf DESC; 
Jan 28 '09 #3

Expert Mod 15k+
P: 31,758
Try something like :
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP [X Percent] PERCENT empUID,
  2.                                empPerf
  3. FROM tblEmp
  4. ORDER BY empPerf DESC
Jan 29 '09 #4

P: 76

Thanks folks...thanks Neo!
This looks like it will work for it fair to say that using the following
Expand|Select|Wrap|Line Numbers
  1. WHERE (((tblEmp.empPerf)<>0))
will exclude any record with a zero value from being calculated?
Feb 4 '09 #5

Expert Mod 15k+
P: 31,758
You're welcome :)
Yes indeed.
Feb 5 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.