Average Of The Best n Results

[EA]

Can this be done in Access?

I have query which returns all the results for a criteria. What I want is
another query which determines the total of the top n results, where n will
be changing number (argument).

Example:

A = 2,5,3,4,2
Sum of A's top 4 results = 14 (n=4)

A = 2,5,3,4,2,0,5,1,5,2
Sum of A's top 8 results = 28 (n=8)

 

[kingston via AccessMonster.com]

If you want to do this in visual queries, you'll have to change the value n
by opening the query in design mode. Otherwise, if all you want is a single
result, a function might work better for you; then n would be an input to the
function.

To do this simply in queries, create a query that retrieves the top n results
from your existing query or modify your existing query. The sort order will
determine the definition of "top". Open the query in design mode, modify the
sort order, open the query properties window (right click in the empty
background), and change the Top Values to your desired number. Next, create
a totals query (icon with summation sign - looks like a sideways M) based on
this result and select Sum for the field.

 

[Allen Browne]

EA, here's a function that does what you need.

It's like the build-in DAvg() function, but accepts an additional argument
for the TOP number of records you want.

There is also the option to specify the ORDER BY clause so you can eliminate
the duplicates, sort descending, or even get the average based on the most
recent records rather than top values (i.e. the TOP can be applied to a
different field.)


Function EAvg(strExpr As String, strDomain As String, Optional strCriteria
As String, _
Optional lngTop As Long, Optional strOrderBy As String) As Variant
On Error GoTo Err_Error
'Purpose: Extended replacement for DAvg().
'Author: Allen Browne ([email protected]), November 2006.
'Requires: Access 2000 and later.
'Return: Average of the field in the domain. Null or error.
'Arguments: strExpr = the field name to average.
' strDomain = the table or query to use.
' strCriteria = WHERE clause limiting the records.
' lngTop = TOP number of records to average. (Ignored if
zero or negative.)
' 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

EAvg = Null 'Initialize to null.

If lngTop > 0& Then
strSql = "SELECT Avg(" & strExpr & ") AS TheAverage " & vbCrLf & _
"FROM (SELECT TOP " & lngTop & " " & strExpr & " " & vbCrLf & _
" FROM " & strDomain & " "
If strCriteria <> vbNullString Then
strSql = strSql & vbCrLf & " WHERE (" & 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
strSql = "SELECT Avg(" & strExpr & ") AS TheAverage " & vbCrLf & _
"FROM " & strDomain & " "
If strCriteria <> vbNullString Then
strSql = strSql & vbCrLf & "WHERE " & 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

Set rs = Nothing

Exit_Handler:
Exit Function

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

 

[Allen Browne]

Have now posted a slightly more powerful version of this function, (and
without the line ending problems in the newsgroup post).

It's here:
http://allenbrowne.com/ser-68.html