Skew and other statistics in Access

[Guest]

Hiya,

I need to run quite a hefty piece of statistical analysis monthly using a
whole heap of data. Whilst I'm aware that Access may not be the best tool in
the world to use, it's all I have.

In a grouped query, I can aggregate for a mean value within the group. I can
also do this for a standard deviation. However, I know that my data will
almost always not follow a symmetrical normal distribution, but will be
positively skewed.

In Excel there is a SWEW() formula which can be used to assess the power of
the skewness (it also has a kurtosis formula). Access seems to be lacking
these, but due to the aggregation and quantity of data, I cannot use Excel.

Is there a way that I can get the skew from Access?

Thanks,

Basil

 

[Douglas J. Steele]

Take a look at FMS's Total Access Statistics
http://www.fmsinc.com/products/statistics/index.html to see whether it has
what you need.

Here's a related post I've saved in my "canned answers" file:

Well, there are Excel functions and then there are Excel Add-ins (which
unfortunately includes Yield). The core worksheet functions can be included
as follows:

1. Open your database, type Ctrl+G to make sure you're in the VBA editor.
2. Choose Tools\References, and scroll down and check Microsoft Excel 10.0
Object Library (or whatever version of the Excel Object Library you have)
3. Use the functions as Excel.WorkSheetFunction.FunctionName, for example if
you type:
?Excel.WorksheetFunction.Ceiling(5,3)
in the immediate pane and hit enter, it should return 6.
4. To use such functions in queries, make a wrapper function by passing
appropriate data to the Excel function and returning the results, for
example:
Function MyCeiling(ByVal iOne As Double, ByVal iTwo As Double)
MyCeiling = Excel.WorksheetFunction.Ceiling(iOne, iTwo)
End Function
5. Call that wrapper function from queries, as in:
Result: MyCeiling([field1],[field2])

Some functions, however, are special cases, as they come from Excel Add-ins.
If you're *really* intent on using that class of functions, you can access
it from automation, see http://support.microsoft.com/?id=198571 (FWIW, I
don't believe SKEW is in this category)

 

[Michel Walsh]

Hi



Mathworld define skewness as... being a little bit fuzzy. See
http://mathworld.wolfram.com/Skewness.html


"The" skweness is generally the ratio of two central moment. An estimator,
eq. 2 of the reference, is thus


SELECT (SELECT AVG(x) FROM myTable) As mean
AVG((x-mean)^3) As k3,
AVG((x-mean)^2) As k2,
k3*k2^-1.5 As TheSkewness

FROM myTable


Note that if you don't explicitly need the central moment k3 and k2, you can
"cut and paste" them inside the last formula.



Other "Skewness" generally involves the mode or the quartiles.



Hoping it may help,
Vanderghast, Access MVP