Conditional Sum in a Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good day. Is there a means to add only the TOP 4 Values in a report Footer. I want the report to show all entries but not add them in the footer. Thank you in advanced.
 
Hello. I'm not sure if you are familiar with VBA, but if
you are, you can create an array that will display the
highest value and then delete it from your remaining
values. By setting an initial counter and using a do
while loop, for loop, or any type of loop you are
comfortable with you can continue to run the loop until
you have your four values. By declaring a second
variable, you can keep a running total of the sum of your
values. (You can then have your program list the remaining
values present in the array if you want.) Have this
program built into a macro in the properties setting
in "code builder". This will make it display in your
report. Hope this helps.

-----Original Message-----
Good day. Is there a means to add only the TOP 4 Values
Click to expand...
in a report Footer. I want the report to show all entries
but not add them in the footer. Thank you in advanced.
 
Ok, here's how I would do this. Make a simple query to return the IDs
of the TOP 4 records. Please post back if you need more specific help
with this, but it sounds like you are familiar with this concept. Then,
add this query to the query that the report is based on, joined by a
Left Join from the ID in the main table in the query. Then make a
calculated field in your query, like this...
Top4Values: [YourValue]*Abs(IsNull([Top4Query].[ID]))
Then, in your report footer, in the control source property of an
unbound textbox, put...
=Sum([Top4Values])
 
Back
Top