Percentile Query?

[artemetis]

Hello!
It's me again.

tblEmp
-->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!

[ADezii]

@artemetis

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:
   

   SELECT Employees.EmployeeID, Employees.empPerf, fCalculatePercentile([empPerf]) AS Percentile
   FROM Employees
   ORDER BY Employees.empPerf DESC;

    
   Public Function fCalculatePercentile(bytPerf As Byte) As String
   Dim MyDB As DAO.Database
   Dim rstPercentile As DAO.Recordset
   Dim intGreater As Integer
   Dim intLess As Integer
   Dim intAllScores As Integer
   intAllScores = DCount("*", "Employees")     'Total Number of Scores
   Set MyDB = CurrentDb
   Set rstPercentile = MyDB.OpenRecordset("Employees", dbOpenForwardOnly)
   With rstPercentile
     Do While Not .EOF
       If bytPerf >= ![empPerf] Then
         intGreater = intGreater + 1       'Increment > Counter
       Else
       End If
       .MoveNext
     Loop
   End With
   rstPercentile.Close
   Set rstPercentile = Nothing
   fCalculatePercentile = Format$(intGreater / intAllScores, "Percent")
   End Function 
    

[ADezii]

Oops, wrong SQL!

 
SELECT tblEmp.empUid, tblEmp.empPerf, fCalculatePercentile([empPerf]) AS Percentile 
FROM tblEmp 
ORDER BY tblEmp.empPerf DESC; 
 

[NeoPa]

Try something like :

SELECT TOP [X Percent] PERCENT empUID,
                               empPerf
FROM tblEmp
ORDER BY empPerf DESC

[artemetis]

@NeoPa

Thanks folks...thanks Neo!
This looks like it will work for me...question.. .is it fair to say that using the following

WHERE (((tblEmp.empPerf)<>0))

will exclude any record with a zero value from being calculated?

[NeoPa]

@artemetis
You're welcome :)
@artemetis
Yes indeed.