By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,473 Members | 3,234 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,473 IT Pros & Developers. It's quick & easy.

How to Calculate skewness and Kurtosis in Access2003 using VB

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
Feb 6 '08 #1
Share this Question
Share on Google+
8 Replies


FishVal
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
Feb 6 '08 #2

MMcCarthy
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.
Feb 7 '08 #3

ADezii
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?
Feb 7 '08 #4

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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM temp_AqR3
  3. 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");
  4.  
regards
Mar 3 '08 #5

Scott Price
Expert 100+
P: 1,384
Have a read through Excel Functions in Access from our Howtos section.

Regards,
Scott
Mar 3 '08 #6

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?

Expand|Select|Wrap|Line Numbers
  1. Sub xlKurt()
  2.    Dim objExcel As Excel.Application
  3.    Set objExcel = CreateObject("Excel.Application")
  4.    MsgBox objExcel.Application.kurt(do i need anything here)
  5.    objExcel.Quit
  6.    Set objExcel = Nothing
  7. End Sub 

Expand|Select|Wrap|Line Numbers
  1. Sub xlSkew()
  2.    Dim objExcel As Excel.Application
  3.    Set objExcel = CreateObject("Excel.Application")
  4.    MsgBox objExcel.Application.Skew(do i need anything here)
  5.    objExcel.Quit
  6.    Set objExcel = Nothing
  7. End Sub
Mar 3 '08 #7

Scott Price
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?

Expand|Select|Wrap|Line Numbers
  1. Sub xlKurt()
  2.    Dim objExcel As Excel.Application
  3.    Set objExcel = CreateObject("Excel.Application")
  4.    MsgBox objExcel.Application.kurt(do i need anything here)
  5.    objExcel.Quit
  6.    Set objExcel = Nothing
  7. End Sub 

Expand|Select|Wrap|Line Numbers
  1. Sub xlSkew()
  2.    Dim objExcel As Excel.Application
  3.    Set objExcel = CreateObject("Excel.Application")
  4.    MsgBox objExcel.Application.Skew(do i need anything here)
  5.    objExcel.Quit
  6.    Set objExcel = Nothing
  7. 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...
Expand|Select|Wrap|Line Numbers
  1. [variable] = Excel.WorksheetFunction.[YourFunctionHere]([parameter1], [parameter2], etc)
Regards,
Scott
Mar 3 '08 #8

FishVal
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 single-field table and four (Access doesn't like nested SQL aggregate functions) queries. [qrySkew] returns Skew for the values in [tbl].
Attached Files
File Type: zip Skew.zip (9.2 KB, 248 views)
Mar 3 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.