Sum and Large FUNCTIONS used in excel (1 Viewer)

jaikaoliver

New member
Local time
Today, 15:22
Joined
Nov 18, 2019
Messages
19
1606639142209.png
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

Immoderate Moderator
Staff member
Local time
Today, 17:22
Joined
Feb 28, 2001
Messages
19,021
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

Wino Moderator
Staff member
Local time
Today, 15:22
Joined
Aug 30, 2003
Messages
34,924
I agree with the normalization comment, but this may help if you stick with this design:

 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:22
Joined
Feb 28, 2001
Messages
19,021
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

error reading drive A:
Local time
Tomorrow, 07:22
Joined
May 7, 2009
Messages
11,446
you can create a Function for your purpose.
see Query1 on the demo.
see Module1 for the source code of the function.
 

Attachments

  • sampleSumLargeUDF.accdb
    432 KB · Views: 17

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:22
Joined
May 21, 2018
Messages
4,464

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

You've got your good things, and you've got mine.
Local time
Today, 18:22
Joined
May 21, 2018
Messages
4,464

Attachments

  • sampleSumLargeUDF.accdb
    448 KB · Views: 13

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:22
Joined
May 21, 2018
Messages
4,464
Here is a little different version using a collection
Code:
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

New member
Local time
Today, 15:22
Joined
Nov 18, 2019
Messages
19
Here is a little different version using a collection
Code:
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
 

Users who are viewing this thread

Top Bottom