Percentile Query?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • artemetis
    New Member
    • Jul 2007
    • 76

    #1

    Percentile Query?

    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
    Recognized Expert Expert
    • Apr 2006
    • 8834

    #2
    Originally posted by 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!
    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:
      Code:
      SELECT Employees.EmployeeID, Employees.empPerf, fCalculatePercentile([empPerf]) AS Percentile
      FROM Employees
      ORDER BY Employees.empPerf DESC;
      Code:
       
      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

    Comment

    • ADezii
      Recognized Expert Expert
      • Apr 2006
      • 8834

      #3
      Percentile

      Oops, wrong SQL!
      Code:
       
      SELECT tblEmp.empUid, tblEmp.empPerf, fCalculatePercentile([empPerf]) AS Percentile 
      FROM tblEmp 
      ORDER BY tblEmp.empPerf DESC;

      Comment

      • NeoPa
        Recognized Expert Moderator MVP
        • Oct 2006
        • 32669

        #4
        Try something like :
        Code:
        SELECT TOP [X Percent] PERCENT empUID,
                                       empPerf
        FROM tblEmp
        ORDER BY empPerf DESC

        Comment

        • artemetis
          New Member
          • Jul 2007
          • 76

          #5
          Originally posted by NeoPa
          Try something like :
          Code:
          SELECT TOP [X Percent] PERCENT empUID,
                                         empPerf
          FROM tblEmp
          ORDER BY empPerf DESC

          Thanks folks...thanks Neo!
          This looks like it will work for me...question.. .is it fair to say that using the following
          Code:
          WHERE (((tblEmp.empPerf)<>0))
          will exclude any record with a zero value from being calculated?

          Comment

          • NeoPa
            Recognized Expert Moderator MVP
            • Oct 2006
            • 32669

            #6
            Originally posted by artemetis
            Thanks folks...thanks Neo!
            You're welcome :)
            Originally posted by artemetis
            question...is it fair to say that using the following
            Code:
            WHERE (((tblEmp.empPerf)<>0))
            will exclude any record with a zero value from being calculated?
            Yes indeed.

            Comment

            Working...