Percentile Aggregate Function

[ajay.kalyan]

I'm having some trouble figuring out how to do a percentile aggregate
function and am hoping someone can help. I need to do something like
this:

SELECT PartID, Percentile(Usage, .8)
FROM PartConsumption
GROUP By PartID

Any ideas. I found some module code for calculating a percentile for a
recordset but am not sure how all that fits into the query that I need
to create. Appreciate any help.

Thanks.

 

[MGFoster]

I'm having some trouble figuring out how to do a percentile aggregate
function and am hoping someone can help. I need to do something like
this:

SELECT PartID, Percentile(Usage, .8)
FROM PartConsumption
GROUP By PartID

Any ideas. I found some module code for calculating a percentile for a
recordset but am not sure how all that fits into the query that I need
to create. Appreciate any help.

There isn't a built-in percentile aggregate function. You seem to be
calling the VBA function "Percentile()" from the query. This is
allowable in JET (Access) queries. Any problems are probably in the VBA
function.

If the VBA function works on a recordset you probably would not call it
from a query, you'd run it in a VBA module.

Post the code for the function Percentile(), then we can determine how
it should be used.

 

[ajay.kalyan]

Here's the code for the Percentile function:

Public Function Percentile(strFilter 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 Usage from PartConsumption WHERE PartID = " &
strFilter, CurrentProject.Connection, adOpenStatic
ReDim dblData(rst.RecordCount - 1)
For x = 0 To (rst.RecordCount - 1)
dblData(x) = rst("Usage")
rst.MoveNext
Next x
Percentile = xl.WorksheetFunction.Percentile(dblData, k)
rst.Close
Set rst = Nothing
Set xl = Nothing
End Function

thats the function I found online, but have no idea how to use it. I
would still prefer to have it done as an aggregate function as posted
above. What are my options?

Thanks

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your original query:

SELECT PartID, Percentile(Usage, .8)
FROM PartConsumption
GROUP By PartID

Doesn't have the correct parameter. The Percentile() function requires
the PartID as the 1st parameter. You'll have to change your query to
this:

SELECT DISTINCT PartID, Percentile(PartID, .8)
FROM PartConsumption

I'm not sure about the DISTINCT. If that doesn't give you distinct
PartIDs then try this:

SELECT PartID, Percentile(PartID, .8)
FROM PartConsumption
GROUP BY PartID, Percentile(PartID, .8)

This may be a slower query.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAS6cIechKqOuFEgEQJa8wCgq5YPUM6nBXAAT2Vm/Ld6F5QObcUAoKtF
NEx/5tOz2RCNS6Ygv0bzfDiJ
=MNfl
-----END PGP SIGNATURE-----