Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Aggregate Function (Access 2002)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Aggregate Function (Access 2002)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aggregate Function (Access 2002)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •