Percentile function in MS Access

  • Thread starter Thread starter renaissanceme
  • Start date Start date
R

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
 
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
Click to expand...

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.
 
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



Smartin said:
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
Click to expand...

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.
Click to expand...
 
Back
Top