





Access

Access domain aggregate functions for geometric, harmonic, and quadratic means


Ease of Use

Easy

Version tested with

2003

Submitted by:

matthewspatrick

Description:

Access has a domain aggregate function DAvg to calculate the arithmetic mean for some set of numbers. The UDFs described in this article may be used to provide similar domain aggregate support for other means, such as the geometric, harmonic, and quadratic means.

Discussion:

In some circumstances, means other than the usual arithmetic means are called for. For example, when faced with a series of annual rates of return, to compute the "average" rate for the whole period, you should use the geometric mean and not the arithmetic mean. A harmonic mean should be used whenever you need to get an "average" for a series of ratios. For example, when computing the overall resistance of a series of parallel electrical resistors, the result is the harmonic mean (which will always be less than the arithmetic mean). Lastly, a quadratic mean (or root mean square) should be used for periodic data (i.e., over time, a graph describes a sine curve), or when both positive and negative data are included, and what you seek is an "average distance from zero" for the various data points.

Code:

instructions for use

Option Compare Database
Option Explicit
Function DGeomMean(FieldName As String, SourceObject As String, Optional Criteria As String = "")
Dim rs As DAO.Recordset
Dim Product As Double
Dim Counter As Long
Dim SQL As String
SQL = "SELECT " & FieldName & " FROM " & SourceObject & IIf(Criteria = "", "", " WHERE " & Criteria)
Set rs = CurrentDb.OpenRecordset(SQL)
Product = 1
Do Until rs.EOF
Product = Product * rs(0)
Counter = Counter + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
If Counter > 0 And Product >= 0 Then DGeomMean = Product ^ (1 / Counter) Else DGeomMean = Null
End Function
Function DPowerMean(FieldName As String, SourceObject As String, Power As Double, _
Optional Criteria As String = "")
Dim rs As DAO.Recordset
Dim RunningSum As Double
Dim Counter As Long
Dim SQL As String
DPowerMean = Null
SQL = "SELECT " & FieldName & " FROM " & SourceObject & IIf(Criteria = "", "", " WHERE " & Criteria)
Set rs = CurrentDb.OpenRecordset(SQL)
On Error Goto Cleanup
Do Until rs.EOF
RunningSum = RunningSum + rs(0) ^ Power
Counter = Counter + 1
rs.MoveNext
Loop
If Counter > 0 Then DPowerMean = (RunningSum / Counter) ^ (1 / Power)
On Error Goto 0
Cleanup:
rs.Close
Set rs = Nothing
End Function

How to use:

 From Access, hit Alt+F11 to get to the VB Editor
 Insert a new regular module into the VB Project (NOT a class module!)
 Paste the code above into your project
 Use the functions DGeomMean and DPowerMean in your queries, reports, forms, or VBA code as desired

Test the code:

 Download the attached archive, and extract the Access file contained in it
 Open that Access file (Special Domain Aggregates.mdb)
 Run the queries qryGeometricMean, qryHarmonicMean, and qryQuadraticMean for demonstrations of how to use the functions

Sample File:

Special Domain Aggregates.zip 24.62KB

Approved by mdmackillop

This entry has been viewed 85 times.

