Create Aggregate function

[Pross]

I am working on a project where I have to calculate the geometric mean of a
set of values from an Access table. I know that it is possible to create my
own functions in a code module and call them from an Access query as I have
done this many times. However I realized that the function I needed to
create would have to be an aggregate function like the AVG function. Is
there a way to create your own aggregate functions in access and if so what
datatype is the input parameter since it is a field?

Any help would be greatly appreciated.

Thank you,

Paul Ross
Hebco, Inc

 

[StCyrM]

Hi Pross

Please find the code for the function GeometricMean. Simply create a new
module and paste the code into it.

Public Function GeometricMean(ByVal TableName As String, FieldName As String)
As Double

Dim dbs As DAO.Database, rst As DAO.Recordset, strSQL As String

Set dbs = CurrentDb()
strSQL = "SELECT " & FieldName & " FROM " & TableName & ";"
Set rst = dbs.OpenRecordset(strSQL)
With rst
If Not (.BOF And .EOF) Then
GeometricMean = 1
End If
Do Until .EOF
GeometricMean = GeometricMean * .Fields(FieldName).Value
.MoveNext
Loop
End With
GeometricMean = Sqr(GeometricMean)

End Function


Hope this helps

Best Regards

Maurice St-Cyr
Micro Systems Consultants, Inc.

 

[Pross]

Thank you Maurice. I would not have thought of doing it that way. The code
works great. Just in case someone else is needing this solution I should
point out that there is a small error in the Geometric mean calculation. The
final result needs to be raised to the power of the inverse of the number
of values in the set and not squared. I have included a revised section of
your code to illustrate this:

With rst
ValueCount = 0
If Not (.BOF And .EOF) Then
GeometricMean = 1
End If

Do Until .EOF
ValueCount = ValueCount + 1
GeometricMean = GeometricMean * .Fields(FieldName).Value
.MoveNext
Loop
End With
GeometricMean = GeometricMean ^ (1 / ValueCount)

Thanks again for your help. You saved me a lot of time.

Paul Ross
Hebco, Inc.

 

[John Spencer (MVP)]

How about using the Sum, Count, Log and Exp functions in a totals query?

Exp(Sum(Log(FieldName))) ^ (1/Count(FieldName))

I think that might give you what you are asking for as long as the numbers don't
get too large.

 

[Pross]

Actually John, your function does exactly what I wanted to do. Thank you
very much.

As is turns out your solution is better that what I had or even creating an
aggregate function since it uses native Access SQL functions. This allows me
to call it from an external application.

While I have your attention for a moment, is it actually possible to create
your own custom aggregate function that you could then use in a totals
query? It seems like there should be but I can't think of it.

Also, as you alluded to in you reply, it is possible in some cases for the
values to get very, very large; even larger that what can be managed with
the double datatype. Do you know if VBA has any way around this limitation?
Perhaps some way to create my own datatype.

Thank you,

Paul Ross
Hebco, Inc

 

[John Spencer (MVP)]

I'm stumped on both your questions - I don't have a solution.