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
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;
Select Manufacturer, Sales, Rank, Percentile from UK_CarSales ORDER BY Sales DESC
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