Does Access have a PERCENTILE function? (2024)

I think you're looking for the format command.

Format(a_variable_or_value, "0.00%")

Mike

Mike,

Look up Percentile in Excel and I think you will see what DJ is looking for. I really don't think it is just the simple Percent.

DJ,

I found the following information about using Excel functions while in Access. The web site where I found this is: http://www.oreilly.com/pub/h/3310

An Excel Function with an Array Parameter

If an Excel function requires an array or table array, you can pass it an array or a multidimensional array created in Access and get back the needed result. Let's look at the code you'd use to call Excel's percentile worksheet function, which returns the kth percentile of values that you specify from a given array of values:

Public Function Percentile(strTbl As String, strFld As String, k As Double)As Double Dim rst As ADODB.Recordset Dim dblData() As Double Dim xl As Object Dim x As Integer Set xl = CreateObject("Excel.Application") Set rst = New ADODB.Recordset rst.Open "Select * from " & strTbl, CurrentProject.Connection,adOpenStatic ReDim dblData(rst.RecordCount - 1) For x = 0 To (rst.RecordCount - 1) dblData(x) = rst(strFld) rst.MoveNext Next x Percentile = xl.WorksheetFunction.Percentile(dblData, k) rst.Close Set rst = Nothing Set xl = Nothing End Function

With this function, we pass the table name and field name to be read into the Access array, which in return is passed into Excel's percentile function along with the kth percentile value that we are looking for in the array of values. It's worth noting that you can pass the function a query name instead of a table, depending on the application's requirements.

Hope this helps,

Thank you Vic. I'm going to play with this to see what I can do with it. Thank you for the article too.

DJ

Vic's tip is a good one. You can use Excel functions in Access by passing array values to those functions. But you can also create a percentile calculation in an Access query.

The following example is based on a table called UK_CarSales. It uses 2 fields from that table: Manufacturer and Sales. Sales is sorted Descending, and 3 calculations extract (1) the rank, (2) the record count and (3) the percentile of each record. SQL of the query is below --

SELECT UK_CarSales.Manufacturer, UK_CarSales.Sales, (SELECT Count(*) FROM UK_CarSales As U WHERE [Sales] > [UK_CarSales].[Sales])+1 AS Rank, (SELECT Count(*) FROM UK_CarSales) AS RCount, ([RCount]-[Rank])/[RCount] AS PercentileFROM UK_CarSalesORDER BY UK_CarSales.Sales DESC;

Denis

Thanks Sydney. Is there any place online where I can read more about this calculation?

DJ

Sydneys' approach is to use subqueries within a query expression. Although yes, he is performing calculations, it may not be a "calculation" in the way I think you are asking.

His example starts with a query statement - which is simplified to:

Select Manufacturer, Sales, Rank, Percentile from UK_CarSales ORDER BY Sales DESC

However, Rank & Percentile do not exist in this table - so he has to define a field that only exists in the query based on the contents of the other fields.

What's where these two come in:

SELECT Count(*) FROM UK_CarSales As U WHERE [Sales] > [UK_CarSales].[Sales])+1 AS RankSELECT Count(*) FROM UK_CarSales) AS RCount, ([RCount]-[Rank])/[RCount] AS Percentile

If you'll notice, the first could be a standalone query, the second does a calculation based on the first (which is why you couldn't just use it by itself) without the first query.

All of it is wrapped together into the overall query -- if you substitue these expressions in place of the simplified query in place of Rank & Percentile -- it takes you back to his original complex query.

Now, if you want to learn about it - pretty much, you just have to learn how to write SQL - step two is go read up on Subqueries (try Google or a book)

@whee -- I get to contribute!
Mike