Percentile function in MS Access

[renaissanceme]

Hi All,

I've reviewed a number of posts dealing with Percentile in these
groups, and I'm still confused. The one I came closest to acheiving a
level of understanding with included this code, and a reference to the
site it came from. I tried to use the code, but something is going
wrong. It's asking me for the parameter value of the table name.
Here's the code and site.

http://www.mvps.org/access/queries/qry0019.htm

' ***************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**************

I've been given a table of data - $ paid for pairs of tickets bought on
an auction site by section for an event. I need to calculate some
percentiles, averages (easy - did that already), and general analysis.

Can someone please help? I'm sure that the key to understanding this
is very simple, but I'm somehow missing it.

Thanks,

Dave

 

[Smartin]

Hi All,

I've reviewed a number of posts dealing with Percentile in these
groups, and I'm still confused. The one I came closest to acheiving a
level of understanding with included this code, and a reference to the
site it came from. I tried to use the code, but something is going
wrong. It's asking me for the parameter value of the table name.
Here's the code and site.

http://www.mvps.org/access/queries/qry0019.htm

' ***************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**************

I've been given a table of data - $ paid for pairs of tickets bought on
an auction site by section for an event. I need to calculate some
percentiles, averages (easy - did that already), and general analysis.

Can someone please help? I'm sure that the key to understanding this
is very simple, but I'm somehow missing it.

Thanks,

Dave

OK I know this seems obvious, but just to be sure... are you passing the
name of the table/query to the function correctly?

If this isn't working for you I have some code modified from a median
routine (found on Microsoft IIRC) that I use to obtain any desired
percentile value... but it's at work. I can post it tomorrow.

 

[Michel Walsh]

Hi,


Also note that the code is subject to value-to-string setting, which is
important if your decimal delimiter is the coma, not the dot: the code won't
work in that case, but adding a INT( ... ) will make it works again, even
in those conditions:


XPercentile = DMin(FName, TName, _
"DCount(""*"", """ & TName & """, """ & FName & _
"<="" & [" & FName & " ]) >= " & _
INT( X * DCount("*", TName) ) )




Hoping it may help,
Vanderghast, Access MVP

Hi All,

I've reviewed a number of posts dealing with Percentile in these
groups, and I'm still confused. The one I came closest to acheiving a
level of understanding with included this code, and a reference to the
site it came from. I tried to use the code, but something is going
wrong. It's asking me for the parameter value of the table name.
Here's the code and site.

http://www.mvps.org/access/queries/qry0019.htm

' ***************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**************

I've been given a table of data - $ paid for pairs of tickets bought on
an auction site by section for an event. I need to calculate some
percentiles, averages (easy - did that already), and general analysis.

Can someone please help? I'm sure that the key to understanding this
is very simple, but I'm somehow missing it.

Thanks,

Dave

OK I know this seems obvious, but just to be sure... are you passing the
name of the table/query to the function correctly?

If this isn't working for you I have some code modified from a median
routine (found on Microsoft IIRC) that I use to obtain any desired
percentile value... but it's at work. I can post it tomorrow.