Microsoft Access Tips for Serious Users

Provided by Allen Browne, November 2006.


Extended DAvg()

The DAvg() function built into Access lets you get the average of a field in a table, and optionally specify criteria.

This EAvg() function extends that functionality, so you can get the average of just the TOP values (or percentage) from the field. You can even specify a different field for sorting, e.g. to get the average of the 4 most recent values.

Using EAvg()

Paste the code below into a standard module. To verify Access understands it, choose Compile from the Debug menu (in the code window.) In Access 2000 or 2002, you may need to add a reference to the DAO library.

You can then use the function anywhere you can use DAvg(), such as in the Control Source of a text box on a form or report.

Use square brackets around your field/table name if it contains a space or other strange character, or starts with a number.

The arguments to supply are:

Examples

These examples show how you could use EAvg() in the Immediate Window (Ctrl+G) in the Northwind database:

Expression Meaning
? EAvg("Quantity", "[Order Details]") Average quantity in all orders.
? EAvg("Quantity", "[Order Details]", , 4) Average quantity of the 4 top orders.
? EAvg("[Quantity] * [UnitPrice]", "[Order Details]", , 5) Average dollar value of the top 5 line items.
? EAvg("Freight", "Orders", , 0.25) Average of the 25% highest freight values.
? EAvg("Freight", "Orders", "Freight > 0", 8, "OrderDate DESC, OrderID DESC") Average freight in the 8 most recent orders that have freight.

The code


Public Function EAvg(strExpr As String, strDomain As String, Optional strCriteria As String, _
    Optional dblTop As Double, Optional strOrderBy As String) As Variant
On Error GoTo Err_Error
    'Purpose:   Extended replacement for DAvg().
    'Author:    Allen Browne (allen@allenbrowne.com), November 2006.
    'Requires:  Access 2000 and later.
    'Return:    Average of the field in the domain. Null on error.
    'Arguments: strExpr     = the field name to average.
    '           strDomain   = the table or query to use.
    '           strCriteria = WHERE clause limiting the records.
    '           dblTop      = TOP number of records to average. Ignored if zero or negative.
    '                             Treated as a percent if less than 1.
    '           strOrderBy  = ORDER BY clause.
    'Note:      The ORDER BY clause defaults to the expression field DESC if none is provided.
    '               However, if there is a tie, Access returns more than the TOP number specified,
    '               unless you include the primary key in the ORDER BY clause. See example below.
    'Example:   Return the average of the 4 highest quantities in tblInvoiceDetail:
    '               EAvg("Quantity", "tblInvoiceDetail",,4, "Quantity DESC, InvoiceDetailID")
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim lngTopAsPercent As Long

    EAvg = Null     'Initialize to null.

    lngTopAsPercent = 100# * dblTop
    If lngTopAsPercent > 0& Then
        'There is a TOP predicate
        If lngTopAsPercent < 100& Then  'Less than 1, so treat as percent.
            strSql = "SELECT Avg(" & strExpr & ") AS TheAverage " & vbCrLf & _
                "FROM (SELECT TOP " & lngTopAsPercent & " PERCENT " & strExpr
        Else                            'More than 1, so treat as count.
            strSql = "SELECT Avg(" & strExpr & ") AS TheAverage " & vbCrLf & _
                "FROM (SELECT TOP " & CLng(dblTop) & " " & strExpr
        End If
        strSql = strSql & " " & vbCrLf & " FROM " & strDomain & " " & vbCrLf & _
            " WHERE (" & strExpr & " Is Not Null)"
        If strCriteria <> vbNullString Then
            strSql = strSql & vbCrLf & " AND (" & strCriteria & ") "
        End If
        If strOrderBy <> vbNullString Then
            strSql = strSql & vbCrLf & " ORDER BY " & strOrderBy & ") AS MySubquery;"
        Else
            strSql = strSql & vbCrLf & " ORDER BY " & strExpr & " DESC) AS MySubquery;"
        End If
    Else
        'There is no TOP predicate (so we also ignore any ORDER BY.)
        strSql = "SELECT Avg(" & strExpr & ") AS TheAverage " & vbCrLf & _
            "FROM " & strDomain & " " & vbCrLf & "WHERE (" & strExpr & " Is Not Null)"
        If strCriteria <> vbNullString Then
            strSql = strSql & vbCrLf & " AND (" & strCriteria & ")"
        End If
        strSql = strSql & ";"
    End If

    Set rs = DBEngine(0)(0).OpenRecordset(strSql)
    If rs.RecordCount > 0& Then
        EAvg = rs!TheAverage
    End If
    rs.Close

Exit_Handler:
    Set rs = Nothing
    Exit Function

Err_Error:
    MsgBox "Error " & Err.Number & ": " & Err.Description, , "EAvg()"
    Resume Exit_Handler
End Function

Comments

The code builds a query statement that retrieves the records from the domain, limited to the criteria.

If you specify a value for dblTop, this query includes a TOP predicate. If the value is less than 1, the value is multiplied by 100 and treated as a PERCENT. It creates a subquery to retrieve the TOP records, and then the main query gets the average of those values. (The subquery in the FROM clause works only in JET 4 and later, so this function will not work in Access 97.)

If you supplied a value for dblTop and also for strOrderBy, the ORDER BY clause is applied in the subquery. This means you can sort the subquery in any way you want. For example, sorting in descending order on a date field would give the average of only the most recent dates. If you do not supply a value for dblTop, strOrderBy is ignored since it makes no difference to the average when all values are retrieved.

If you ask for top values but do not use strOrderBy, the function will use the field named in strExpr in descending order. Be aware that JET behaves differently than SQL Server if there is a tie. If you asked for the TOP 4 values, but the 5th record has the same value as the 4th, JET is unable to distinguish the values, and so will give the average of the TOP 5 instead of the TOP 4. To avoid this issue, you should supply the sorting clause, and include the primary key value so there can no longer be a tie. For example, instead of:
    EAvg("Freight", "Orders", , 12)
use:
    EAvg("Freight", "Orders", , 12, "Freight DESC, OrderID")


HomeIndex of tipsTop