P: 4

Dear All,
I want to write an user defined aggregate function to calculate skewness and Kurtosis of data in a column of a table. Could you please help me to do that.
I know how to add the code to make it work in a query but struugle to write my own code
Any help would be greatly appreciated
Regards
Chris
 
Share this Question
Expert 2.5K+
P: 2,653

Hi, Chris.
The formulas to calculate skewness and Kurtosis are quite simple. What kind of help do you actually want?
Regards,
Fish
  Expert Mod 10K+
P: 14,534

If as I suspect you are trying to store these fields in table then I should tell you that storing calculated field values is not good if they are subject to change. Calculating the values in a query which you say you have done successfully is actually the way to go.
However, if you then want to store these values historically in a table i.e. not subject to change but a new record for every value then you would just amend a record based on the query results.
  Expert 5K+
P: 8,597

Dear All,
I want to write an user defined aggregate function to calculate skewness and Kurtosis of data in a column of a table. Could you please help me to do that.
I know how to add the code to make it work in a query but struugle to write my own code
Any help would be greatly appreciated
Regards
Chris
These Functions ( SKEW(), KURT()) are clearly defined and exist in Excel. You can access either one or both of these Excel Functions from Access, pass to them from 1 to 30 Arguments which can be either Numbers, Names, or an Array, then generate a result. Excel has already done the work for you. Would this be applicable in your case?
 
P: 4

I wish to store them in a table for a data set at a point in time. I notice someone has said i can integrate the excel functions in to access how do i go about doing this ?
I currently have a query that i calculate mean median percentiles min max values and wish to add the Kurtosis and skewness too.
If it helps i have pasted an sql version of my query  SELECT temp_AqR3.Determinand_Name, temp_AqR3.Units, temp_AqR3.Aquifer_Report, Count(temp_AqR3.AmendedResult) AS [Number of Samples], Count(temp_AqR3.Sign) AS [Non Detects], Min(temp_AqR3.AmendedResult) AS Min_, Max(temp_AqR3.AmendedResult) AS Max_, Avg(temp_AqR3.AmendedResult) AS Mean, Median("temp_AqR3","AmendedResult") AS Median, PercentileRst("temp_AqR3","AmendedResult","0.05") AS 5, PercentileRst("temp_AqR3","AmendedResult","0.95") AS 95, StDev(temp_AqR3.AmendedResult) AS StDev

FROM temp_AqR3

GROUP BY temp_AqR3.Determinand_Name, temp_AqR3.Units, temp_AqR3.Aquifer_Report, Median("temp_AqR3","AmendedResult"), PercentileRst("temp_AqR3","AmendedResult","0.05"), PercentileRst("temp_AqR3","AmendedResult","0.95");

regards
 
P: 4

I have now added the excel libary to vb
what i am struggling with is how do a write the script to call the excel function .
I see from the ms office site that it needs to start but how do i finish it ?
Is this correct?  Sub xlKurt()

Dim objExcel As Excel.Application

Set objExcel = CreateObject("Excel.Application")

MsgBox objExcel.Application.kurt(do i need anything here)

objExcel.Quit

Set objExcel = Nothing

End Sub
 Sub xlSkew()

Dim objExcel As Excel.Application

Set objExcel = CreateObject("Excel.Application")

MsgBox objExcel.Application.Skew(do i need anything here)

objExcel.Quit

Set objExcel = Nothing

End Sub
  Expert 100+
P: 1,384

I have now added the excel libary to vb
what i am struggling with is how do a write the script to call the excel function .
I see from the ms office site that it needs to start but how do i finish it ?
Is this correct?  Sub xlKurt()

Dim objExcel As Excel.Application

Set objExcel = CreateObject("Excel.Application")

MsgBox objExcel.Application.kurt(do i need anything here)

objExcel.Quit

Set objExcel = Nothing

End Sub
 Sub xlSkew()

Dim objExcel As Excel.Application

Set objExcel = CreateObject("Excel.Application")

MsgBox objExcel.Application.Skew(do i need anything here)

objExcel.Quit

Set objExcel = Nothing

End Sub
Look carefully at the example in the link I gave you.
The syntax is from line 30 in the code of the example...  [variable] = Excel.WorksheetFunction.[YourFunctionHere]([parameter1], [parameter2], etc)
Regards,
Scott
  Expert 2.5K+
P: 2,653

Hi, all.
IMHO Excel functions are not suitable in this case.
It gets as argument:  up to 30 arguments
 array
 worksheet range
All this will be rather difficult to implement in VBA or/and SQL.
Though array looks like a best option it would require relatively slow code to fill it with required values from a table.
I would suggest to use math calculation  it requires: count of values, average and standard deviation. All may be easily obtained by SQL or domain aggregate functions. The formulas themselves you can found in Excel help concerning KURT() and SKEW() functions.
Regards,
Fish.
P.S. Here I'm attaching db with one singlefield table and four (Access doesn't like nested SQL aggregate functions) queries. [qrySkew] returns Skew for the values in [tbl].
    Question stats  viewed: 6644
 replies: 8
 date asked: Feb 6 '08
