Caculating Percentiles in MS Access

[MEdmond]

Is there a way to incorporate into Access query results through Visual Basic code or (some other method) the Excel basic statistical functions that MS Access lacks (e.g, percentiles)? I have looked at a 3rd-party add-ins but it didn't seem to be convenient to use for my purposes, yield ing results in a new table that cannot easily be linked back to the data.

Thanks

 

[jebry]

Hi!

To get at the actual functions you need to set a reference to Excel in a code module and then you can do the following:

Public Function YourFunction(NeededData) As Double or whatever

Dim Xcel As Excel.Application

YourFunction = Xcel.WorksheetFunctions.ExcelFunction(NeededData)

End Function

Obviously the 'NeededData' will be anything the excel function needs to return a value. Alternatively you can just write your own functions. Go into Excel and use the help to try and determine what exactly a function does and then code it in Access.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[MichaelRed]

thread710-170007



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MEdmond]

Thanks Jeff for your reply. I am sure it is pointing me in the right direction. However, I am having difficulty with defining the function properly I beleve . It must be an array function to Calculate percentiles using the Excel PERCENTILE function. Then I have to access it in an EXCEL query. Apparently I don't make it a totals query, for then it only expects the standard aggregate fucntions in ACCESS.
However, I am not sure how the function I create will work otherwise.

Below is my attempt at VB code per your example:


Dim Xcel As Excel.Application
Dim PrcntlDblArray(100)
Prcntl10 = Xcel.WorksheetFunctions.Percentile
(ParamArray PrcntlDblArray(), 10)

VB doesn't like the ParamArray command. I have tried leaving it out but then I get an error when trying to use the function in an Access query.

If you can provide any more guidance, it would be appeciated.

Thanks.