Does Access have a PERCENTILE function?

[djl0525]

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

DJ

 

[VicRauch]

Access does not have a percentile function. Looks like you will have to create your own function. I looked at the documentation on the percentile function in Excel and I don't understand how the percentile is being calculated. If you know the calculation, but don't know how to write the function, pass along the calculation and someone here will help you with the function.

 

[mdmilner]

I think you're looking for the format command.

Format(a_variable_or_value, "0.00%")

Mike

 

[VicRauch]

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.

 

[VicRauch]

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,

 

[djl0525]

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

 

[SydneyGeek]

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 Percentile
FROM UK_CarSales
ORDER BY UK_CarSales.Sales DESC;

Denis

 

[mdmilner]

Yeup, I don't do a lot of work with excel and didn't even bother to look up the function. I can see by reading it now and reading the proposed solutions that the format command really had little to do with this answer although it might be relevant at a later step.

 

[djl0525]

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

DJ

 

[mdmilner]

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 Rank

SELECT 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