Calculating percentile with query

[glmtd]

Hello everyone. My data in one of the tables is stored in a long format, so 100 lines with numbers under each column. I would like to be able to calculate Percentile in ACCESS 2010 in a query. In Excel I use this formula =PERCENTILE(A1:A100, 0.16). Similarly, I would also like to also recreate this function =COUNTIF(A1:A100,"<=2")/100 in a query as well, to find the percentage of values that are below or equal to 2, or =COUNTIFS(A1:A100,">2",A1:A100,"<=64")/100 to calculate percentage between values larger than 2 and below 64.




Thanks!

[June7]

I recommend building a report to do these calcs in.

Review this thread http://www.mrexcel.com/forum/showthr...NTILE-function

Here is a reference on using subqueries http://allenbrowne.com/subquery-01.html

[glmtd]

Thank you for that June7.

I've found a script that does work and calculates the percentile, but it needs some modifications for my data. My data is structured this way, but under each project I have 100 size numbers:

Project Size
Project1 1
Project1 5
Project1 3
Project2 2
Project2 4
Project3 7

I would like to be able to calculate percentile for each Project, and not for all of the data under size field name.

Here is the script that calculates percentile for all of the data:

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

[June7]

You are calling this function in a query or textbox ControlSource?

The DCount will need criteria for the project ID and whatever is needed to exclude the undesired Size data..

[glmtd]

I am calling this function in a guery. Could you provide me a with an example on how to specify the range of data in DCount, I am quite clueless in script. Thank You!

[June7]

Where does the X value come from? Is it a field in the query? Is Project the fieldname fed into the FName argument?

Are you calling this function from other queries or code? Might be able to put the nested aggregate functions directly in the query.

A report can group your data and do the percentile calcs.

Domain aggregates can really slow query performance and, as you are finding, be cumbersome because any filter criteria used in the query must be replicated in the domain function.

The function uses a DCount with a nested DCount. I expect both need the same filter criteria applied. My head is spinning.

A report handles the dirty work for you. It allows display of detail records and summary calcs.

Unless there is some compelling requirement to have it all accomplished within a query, I recommend a report.

Might find this thread of interest https://www.accessforums.net/queries...ile-40273.html