Does Access have a PERCENTILE function?

[djl0525]

How can I calculate percentile in Access? Is there a percentile function?

DJ

 

[djl0525]

Cool beans Mike!

 

[djl0525]

Vic, I came across this PERCENTILE calculation at http://www.groupacg.com/AqryTip.htm#PCTILE.

Percentile = Mean (Avg) Value + (Standard Deviation * Z-table value)

Can a function be written based on this calculation?

DJ

 

[SydneyGeek]

Awake now...

The Percentile calculation that I used is based on another way of calculating the percentile --
[Percentile] = ([RCount]-[Rank])/[RCount], where [RCount] is the calculation that gives the number of records in the query.

If you want to use this in your query, select the 2 fields you want to use (one will be descriptive, one will be the numeric field whose percentile you want).

Copy the SQL I gave you, into Notepad

Replace UK_CarSales with YourTableName (or [Your Table Name] if there are spaces)
Replace Manufacturer with YourDescriptiveField (eg Name)
Replace Sales with YourNumericField (eg Score)
Note: for the fields, make sure that the names are in square brackets if they have spaces, or Access will spit the dummy.

Now, start a new query. Dismiss the dialog that prompts you to pick a table.
click the SQL icon at top left, and paste the whole SQL statement from Notepad into the SQL view. Make sure you completely replace whatever was there.
Take a look, and see how you go.

You should end up with 3 fields to the right of your 2 table fields. The first will be a ranking, starting at 1. The second is the record count, it will be the same for every record. The third is the percentile, to several decimal places.

To get every item from percentile 90 to percentile 95, you use the expression Between 90 And 95 in the Criteria row of the Percentile field.

Denis

 

[djl0525]

Thanks! I'll try this!

DJ

 

[shellp]

Hi

I know this is a VERY old thread but I see some of the same people are still here so hopefully there will be a response.

In the example used, it appears that the data was already aggregate, correct?

My data is patient level data but I want to find out the percentile per week - is this possible?

Also, I don't want to use all records to count, I want a field I have called denom to be summed.

Also, I'm not understanding where you enter the percentile "n"...I would like 90th percentile (so 9/10 patients treated within this LOS per day/week).

Any and all help greatly appreciated.

 

[AccessRapel]

Hi,
I have the following problem with the percentile function.
I have an access data base, where I have a query which calculates average, minimum and maximum. Specified per customer and per month.
I want to add the 90th percentile to it.

For example: Table1
customer month value
a 1 1
a 1 2
a 2 1

Query1 of table 1
customer month average min max 90th
a 1 1,5 1 2 1,8
a 2 1 1 1 1

How can I add the 90th percentile to this query?

Kind regards

 

[funkykizzy]

Greetings!!

I REALLY hope that there are some folks out there with the answer to this question, but I am having the same issue as the last 2 members:
I can group my data in a query to get annual values, and I can calculate a percentile by calling a function, but I cannot find a way to calculate a percentile of my grouped data. Is this possible? I need to calculate the 5th, 50th, and 95th percentiles of annualized data (i.e., many years with many values per year and I want X percentile per year).

I got the following code from Access Help and How-to - Microsoft Office Calculating percentiles in Access - UtterAccess Discussion Forums

Public Function fnXPercentile(strFName As String, strTname As String, intX As Double) As Double
' dcount("*","tablename", "Field<=" & [Field] ranks
' the field between 1 and n including nulls
fnXPercentile = DMin(strFName, strTname, "Dcount(""*"", """ & strTname & """, """ & strFName & "<="" & [" & strFName & " ]) >= " & intX * DCount("*", strTname))
End Function

And I am calling the function as an expression in a Make Table Query.

Any thoughts? PLEASE??

Thank you SO MUCH in advance!

 

[mountainclimber11]

This is a really old thread, but I am looking for a sql query that duplicates the value returned by the PERCENTILE.EXC function. For example, a 25th percentile on these values:

1.3628, 1.4856, 1.7543, 1.4117

Returns approx. 1.37, not approx 1.36.