Using Statistical Functions in Access Queries

[Rick]

Is there a way to use Excel's statistical functions in an Access query? I
would like to generate a gamma distribution and can do so in Excel using the
GAMMADIST() function. However, it does not appear as though Access can
utilize these functions? Is there something I need to do to be able to
access these functions?

THanks,

Rick

 

[Jeff Boyce]

Rick

No guarantees. Excel requires data in a particular format/arrangement to be
able to run its functions. A well-normalized Access database is NOT a
spreadsheet!

If you open a code module, click on Tools, then References. Check the MS
Excel object model. Your Access database is now connected to the object
model (and functions) available in Excel.

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

P.S. You may need to massage your Access data into an "un-normal" format to
use the Excel function...

 

[Rick]

Thank you Jeff. I had already tried that but do not seem to be able to use
the function. Does setting the reference as you described grant me access to
the Excel object model as well as the inherent spreadsheet functions?

Thanks for any assistance you may be able to provide.

Rick

 

[Jeff Boyce]

Yes.

What happens when you try? (have you re-organized your data points to match
what Excel's function is expecting?)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Sheridan]

Rick:

The GAMMADIST function is an Excel worksheet function, so having established
a reference to Excel you need to call it in Access as follows :

Excel.WorksheetFunction.GammaDist(10,9,2,False)
or:
Excel.WorksheetFunction.GammaDist(10,9,2,True)

Rather than calling it directly, though, you'd be better wrapping it in a
VBA function:

Function GetGammaDist(x, alpha, beta, cumulative)

GetGammaDist = _
Excel.WorksheetFunction.GammaDist(x, alpha, beta, cumulative)

End Function

As regards your second question, you can work with Excel objects from Access
using automation. Here's a simple example for opening a workbook:

Public Sub OpenWorkbook(strWorkBook As String)

Dim appExcel As Excel.Application

Set appExcel = GetExcelApp()

appExcel.Workbooks.Open FileName:=strWorkBook
appExcel.Visible = True

End Sub

Private Function GetExcelApp() As Object

' if Excel open return reference to it
' else establish reference to it
On Error Resume Next
Set GetExcelApp = GetObject(, "Excel.Application")
If Err.Number = 429 Then
Set GetExcelApp = CreateObject("Excel.Application")
End If

End Function

For detailed advice on working with the Excel object model you'd probably be
better off posting in the Excel discussion group. Speaking for myself I only
have a passing acquaintance with the Excel model, and my understanding of its
statistical functions is roughly on a par with my ability to walk on water.
I hope the above is useful, but anything beyond that and I'd be sinking fast.

Ken Sheridan
Stafford, England

 

[Rick]

Thanks Ken - this is perfect. Much appreciated.

Rick

 

[James A. Fortune]

Is there a way to use Excel's statistical functions in an Access query? I
would like to generate a gamma distribution and can do so in Excel using the
GAMMADIST() function. However, it does not appear as though Access can
utilize these functions? Is there something I need to do to be able to
access these functions?

THanks,

Rick

After reading this thread I decided to take a try at a Gamma function in
Access. The Gamma function could be part of a potential solution in
Access to this problem. My first attempt was based on an Euler integral
form found at www.mathworld.com.

http://mathworld.wolfram.com/GammaFunction.html

gamma(z) := Integral from 0 to 1 of [ln(1/t)] ^ (z-1) dt.

To solve Integral from 0 to 1 of G(t,z) dt (z fixed), brute force method:

'---Begin Module Code---
Public Function Gamma(dblZ As Double) As Double
Dim dblAreaSum As Double
Dim lngN As Long
Dim lngI As Long
Dim dblDeltaT As Double

lngN = 10000000
dblDeltaT = CDbl(1 / lngN)
dblAreaSum = 0
For lngI = 1 To lngN
dblAreaSum = dblAreaSum + G((lngI - 0.5) * dblDeltaT, dblZ)
Next lngI
Gamma = dblAreaSum * dblDeltaT
End Function

Public Function G(dblT As Double, dblZ As Double) As Double
G = Log(1 / dblT) ^ (dblZ - 1)
End Function
'---Begin Module Code---

MsgBox (Gamma(4)) => 5.9999651 (instead of the actual value of 6.00000)

It ran a little slowly.

The next step was to use two different step sizes with the step size
above for the first 10% of the time domain and then a step size that is
10 times larger. That ran about nine times faster and produced almost
exactly the same result for an input value of 4. I'm not suggesting
abandoning the Excel functions at all here. This was just a small
programming exercise. I think the equivalent of factorials is going to
play havoc with the numerical accuracy without some clever programming
so the warning about thorough testing applies doubly to this problem.

James A. Fortune
(e-mail address removed)