Statistics functions in Access 2010

I don't know why, but there are quite a number of statistics functions that are in Microsoft Excel that are not included in Microsoft Access. Some of these are CONFIDENCE.T, SKEW, KURT, MEDIAN, MODE, etc. I found two options to get use these functions in Access 2010.

Option 1: Add a module and create some domain functions

This is the most attractive to me because it is fast and doesn't require using the external Excel worksheet object. Patrick G. Matthews posted excellent instructions and code here: http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2529-Median-Mode-Skewness-and-Kurtosis-in-MS-Access.html.

After creating my module and pasting in his source code, I created a query:

SELECT DMode("length","Numbers") as Mode;

This gives me the MODE of the column "length" in the table "Numbers". You can pass along a where clause as necessary.

Option 2: Call out to the Excel worksheet object.

Unfortunately, I couldn't find any snazzy VBA code from Patrick to give me the CONFIDENCE.T function for the excel worksheet I was translating for an Access report. I did, however, find that I could write a function like this:

Function fConfidence(a_alpha As Double, a_count As Long, a_stdev As Double)

    Dim objExcel As Object
    
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = False

    fConfidence = objExcel.WorksheetFunction.CONFIDENCE_T(a_alpha, a_stdev, a_count)
    
    Set objExcel = Nothing
    

End Function

This worked fine once I realized that I needed to call "CONFIDENCE_T" and not "CONFIDENCE.T" (the error I was getting said that the function didn't have the right number of parameters. Not very helpful). Thanks to http://msdn.microsoft.com/en-us/library/office/ff838173.aspx I found the name difference.

Now I can call a query to get the various statistics information I need for the report:

SELECT DMedian("length","Numbers") AS Median, DAvg("length","Numbers") AS Average,
 DKurtosis("length","Numbers") AS Kurtosis, DSkewness("length","Numbers") AS Skewness,
 DMode("length","Numbers") AS Mode, DMax("length","Numbers") AS [Max], DMin("length","Numbers") AS [Min],
 DStDev("length","Numbers") AS StdDev, DCount("length","Numbers") AS [Count], StdDev/Sqr(Count) AS StdError,
 DSum("length","Numbers") AS [Sum], Max-Min AS Range, DVar("length","Numbers") AS SampleVariance,
 fConfidence(0.05, Count, StdDev) As ConfidenceLevel

Handy!