Aggregate Function (Access 2002)

[WTH]

Hi

I know Access only has limited functions in this area, so I hoping someone has already created some code for the following (if not I will have a go myself). The function I need is equivalent to the Large function in Excel (LARGE(array,k) - Returns the k-th largest value in a data set). A search of the forum and one or two websites has not provided an answer.

For my purposes, I need to determine the maximum and the next highest number for a group, then add them together and divide them by 1000. Obviously, selecting the maximum is not a problem, but determining the next highest is more problematic. In the absence of any existing code, my thoughts on code required would to use a loop to determine the 2nd highest value for a group.

Any suggestions would be appreciated.

Thanks & Regards
WTH

[HansV]

If you want to know the next to highest value of a field in a table, you can do it with two queries:
1. Create a query based on the table.
2. Add the relevant field to the query grid.
3. Sort descending on this field.
4. Set the Top Values property of the query to 2.
5. Save this query.
6. Create a new query based on the first one.
7. Add the field to the query grid.
8. Select View | Totals.
9. Set the Total option for the field to Min.

If you want to group by some field, and obtain the next-to-highest value in each group, it's more complicated. Please post back if you need that.

[WTH]

Hi Hans

Thanks for the advice. My initial thoughts were along the lines you suggested, but decided to use a function which I could use in a query. This approach I hope will save on extra queries for calculations. The following seems to work ok, although I admit a) it will run slower than a query and [img]/forums/images/smilies/cool.gif[/img] perhaps it could have been done by a query.

Function LargeFunc(ActiveID As Integer) As Double

Dim sqlstr As String
Dim Maxvalue As Double
Dim SecMaxvalue As Double
Dim Db As DAO.Database
Dim rst As DAO.Recordset

sqlstr = "SELECT NEDI.Active_ID, NEDI.STMR, Dietary_Figures.NZ_High, NEDI.STMR*Dietary_Figures.NZ_High AS SumNZHigh " & _
"FROM NEDI INNER JOIN Dietary_Figures ON NEDI.SubjectID = Dietary_Figures.Subject_ID WHERE NEDI.Active_ID = " & ActiveID & _
" Group by NEDI.Active_ID, NEDI.STMR, Dietary_Figures.NZ_High;"
Set Db = CurrentDb
Set rst = Db.OpenRecordset(sqlstr)


Do While Not rst.EOF
If Maxvalue >= rst.Fields(3) Then
'do nothing
Else
Maxvalue = rst.Fields(3)
End If
rst.MoveNext
Loop
rst.MoveFirst
Do While Not rst.EOF
If Maxvalue <= rst.Fields(3) Then
Else
If SecMaxvalue >= rst.Fields(3) Then
Else
SecMaxvalue = rst.Fields(3)
End If

End If
rst.MoveNext
Loop

LargeFunc = (Maxvalue + SecMaxvalue) / 1000

rst.Close
Db.Close

End Function

Regards
WTH