Home  |  About  |   Search   

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

RunCommand Constants

Terms of Use


 

Queries: Quartiles, Percentiles

Author(s)
Michel Walsh

If we define the x-percentile as the minimum value for wich x percent of the values in the sample are lower or equal to it, then we can issue a simple x-percentile formula using the old friends Dxxx functions:

DCount("*", "tableName", "Field<=" & [Field] )

is ranking the Field, and since there is N records, N = DCount("*", "tableName"), we just need the Minimum field value for where the rank is higher or equal to x* N:

' ***************Code start**************
' This code was originally written by Michel Walsh. 
' It is not to be altered or distributed, 
' except as part of an application. 
' You are free to use it in any application,  
' provided the copyright notice is left unchanged.
'
' Code courtesy of
' Michel Walsh
'
Public Function XPercentile(FName As String, _
                        TName As String, _
                        X As Double) _
                        As Double
'   FName = Field name
'   TName = Table name
'   x = decimal percentile (0.68 for 68%)

'   Return the minimum value for which x% of
'   the values are lower or equal to it
    XPercentile = DMin(FName, TName, _
                "DCount(""*"", """ & TName & """, """ & FName & _
             "<="" & [" & FName & " ]) >= " & _
             X * DCount("*", TName))
End Function
' ***************Code start**************

Note that I assume no NULL values.


1998-2009, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer