How to Sum big numbers from the field

Patricksss

New member
Local time
Today, 22:54
Joined
Apr 12, 2022
Messages
4
Hello everyone. I wanted to ask if there is a way i can sum large numbers from a column of many values. For example the way in excel i can implement it as:
=SUM(LARGE(B1:B30,{1,2,3,4,5,6}))
Is there a way i can implement the same logic of adding only 6 large numbers?
 
If this is Access, you can do this in two stages.
First, write a query to sort the table on the number field in question, then have it return the TOP 6 values. (If in doubt, look up keyword TOP).
Second, write a SUMMATION query on that field based on the first query.
 
Yes it is actually in access. I am working on the report were i want to add the 6 highest grades together. Kindly check the attatchment. The Grade is being generated by a textfield with control source of a query.
 

Attachments

  • Capte.PNG
    Capte.PNG
    8.9 KB · Views: 131
would help to see some example input and outputs since with the LARGE function you specify which rows to return - and you didn't say '6 largest', you said '6 large numbers'

so if your requirement was stated as

=SUM(LARGE(B1:B30,{1,2,8,9,11,12}))

the top predicate won't work

Edit - now clarified ' add the 6 highest grades '
 
or use sub-query:

SELECT sum([theFieldToSum]) from
(select top 6 [theFieldToSum] from yourTableName order by [theFieldToSum] desc)
 
only thing to watch with the top predicate is if you have the values

10
10
9
9
8
7
8
9
9

and you did top 4 you will get returned
10
10
9
9
9
9

to resolve include an extra field with a unique value and use a second query

SELECT top 6 [theFieldToSum] from
(select top 6 [theFieldToSum], someuniquevalue from yourTableName order by [theFieldToSum] desc) A

and then sum that

select sum(thefieldtosum) from
(SELECT top 6 [theFieldToSum] from
(select top 6 [theFieldToSum], someuniquevalue from yourTableName order by [theFieldToSum] desc) A) B
 
Last edited:

Users who are viewing this thread

Back
Top Bottom