Provided by Allen Browne, November 2006.
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.
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:
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. |
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
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")
Home | Index of tips | Top |