Sum and Large FUNCTIONS used in excel (1 Viewer)

[jaikaoliver]

Hell can find an equivalent of the " =SUM(LARGE(A2:C2,{1,2})) " used in excel for my database for same records and return same results

 

[The_Doc_Man]

Access does not have an equivalent to the LARGE function ("find the largest") for groups of things. However, if you have a set of fields there is such a thing as a DMax() function to give the largest value from all values in the given field of the recordset. DMax does not work "horizontally" though. In Excel terms, DMax() works on columns but not on rows. If you wanted something to give the maximum value across multiple rows, you have to program your own with VBA. Though USUALLY, if you were looking for such a thing, it would be a sign of a poorly designed Access application because of normalization issues.

 

[pbaldy]

I agree with the normalization comment, but this may help if you stick with this design:

Microsoft Access tips: MaxOfList() and MinOfList() functions

Code for selecting the minimum and maximum values of a list of values in a Microsoft Access database. Demonstrates ParamArray.

allenbrowne.com

 

[The_Doc_Man]

Figures that Allen Browne would have an example of this. Like I said, Access doesn't have it intrinsically but you can roll your own - or let someone else like Allen roll it for you.

 

[arnelgp]

you can create a Function for your purpose.
see Query1 on the demo.
see Module1 for the source code of the function.

 

[jaikaoliver]

you can create a Function for your purpose.
see Query1 on the demo.
see Module1 for the source code of the function.

Hey I got this error what should I do

 

[MajP]

VBA ArrayList Automation Error​

You may encounter the VB Run-time Error ‘-2146232576 Automation Error’ when trying to get the ArrayList to work. Or sometimes your code has been working for a long time and then suddenly this error appears.

This is caused by not having the correct .Net Framework version installed. The correct version is 3.5. It doesn’t matter if you have a later version like 4.7, you must have 3.5 installed

 

[MajP]

I used Chip Pearson's Qsort to avoid this issue

Sorting Arrays In VBA

www.cpearson.com

 

[MajP]

Here is a little different version using a collection

Public Function SumLarge_MajP(ByVal nthLargest As Integer, ParamArray list() As Variant) As Double
    Dim i As Integer
    Dim j As Integer
    Dim colSum As New Collection
    Dim dbl As Double
    
    For i = 0 To UBound(list)
      If colSum.Count = 0 Then
       colSum.Add list(i)
      Else
        For j = 1 To colSum.Count
          If list(i) > colSum(j) Then
            colSum.Add list(i), , j
            Exit For
          End If
          If j = colSum.Count Then colSum.Add list(i)
        Next j
      End If
    Next i
    
    For i = 1 To nthLargest
        dbl = dbl + colSum(i)
    Next
    
    SumLarge_MajP = dbl
End Function

 

[jaikaoliver]

Here is a little different version using a collection

Public Function SumLarge_MajP(ByVal nthLargest As Integer, ParamArray list() As Variant) As Double
    Dim i As Integer
    Dim j As Integer
    Dim colSum As New Collection
    Dim dbl As Double
   
    For i = 0 To UBound(list)
      If colSum.Count = 0 Then
       colSum.Add list(i)
      Else
        For j = 1 To colSum.Count
          If list(i) > colSum(j) Then
            colSum.Add list(i), , j
            Exit For
          End If
          If j = colSum.Count Then colSum.Add list(i)
        Next j
      End If
    Next i
   
    For i = 1 To nthLargest
        dbl = dbl + colSum(i)
    Next
   
    SumLarge_MajP = dbl
End Function

Thanks Alot @MajP IT WORKS FINE
Thank you once again