MS Report - Sum Function

Robert Schumann
Robert Schumann used Ask the Experts™
on
Hi - I'm trying to sum the amount in a report that is Grouped by Orders, the group will show Capital and Expense orders.  Trying to show separate total amounts for Capital and Expense orders in the Report Header.  

I'm using a text field with    =Sum((SELECT [Amount] WHERE [OrderType] = "Capital")) but getting an #Error


Thanks
Bob
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
You can't embed a select statement like this.  Sum() works on the domain of the recordset.  If you want to sum something else, use the domain function - DSum("Amount", "yourtablename", "[Order Type] = "Capital")

However, I think you probably need more criteria than just "=capital".
Robert SchumannFacilities Planner

Author

Commented:
Your right, needed more criteria.  

Trying this but it's giving me the total amount for the entire table.  Not filtering just the Asset items by Expense.

=DSum("Amount","PurchaseOrders",[Asset]="99992427" And [OrderType]="Expense")
Distinguished Expert 2017

Commented:
The statement is not delimited correctly.  There are three arguments, the field you want to sum, the table or query that is the domaiin, and the criteria.

=DSum("Amount","PurchaseOrders","[Asset]='99992427' And [OrderType]='Expense'")

Notice how the criteris is all encapsulated into a string.  Getting the syntax correct when you need embedded quotes can be tricky.

Hard-coding the asset is probably the wrong approach.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
no points please:

The syntax for the DSUM() would look something like:

=DSum("Amount","PurchaseOrders","[Asset]=99992427 And [OrderType]='Expense'")

of, if [Asset] is a string:

=DSum("Amount","PurchaseOrders","[Asset]='99992427' And [OrderType]='Expense'")

Dale
Robert SchumannFacilities Planner

Author

Commented:
Pat and Dale

Your both right.  It does show the correct total for the Order Type of Capital or Expense.  But it's putting the total in both Groups.

If I try this I get an #Error
=DSum("Amount","PurchaseOrders","[Asset]='99992427' And [OrderType]='Expense' And ‘[OrderType]=’Capital’")
Robert SchumannFacilities Planner

Author

Commented:
Correction - there are two groups, Capital and Expense.  It's putting the same total in both groups.  In this it's only identifying Expense, it should only put the amount in the Expense group.  However it's putting the correct amount in both the Capital and Expense group.  I still need to add the group Capital to the equation.

=DSum("Amount","PurchaseOrders","[Asset]='99992427' And [OrderType]='Expense'")
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
you have an extra ' before the [OrderType] field, try:

=DSum("Amount","PurchaseOrders","[Asset]='99992427' And [OrderType]='Expense' And [OrderType]=’Capital’")
Robert SchumannFacilities Planner

Author

Commented:
oops, just saw that.

Getting an #Error.  Is it possible what I'm trying isn't possible?  Would there be a better way?
Robert SchumannFacilities Planner

Author

Commented:
Guys,

I did a work around.  

I created another text field for Capital.  I needed these to do calculations in the report header.  I'll reference these two text boxes in the Header and simply hide the fields within the groups.

Thank you both.
Distinguished Expert 2017

Commented:
You do realize that the Report header only ever prints ONCE at the beginning of the report.
Hamed NasrRetired IT Professional

Commented:
Did you try to use sum over Group/All. Split Expense and Capital in detail section.
Setting the value in header comes next.
Hamed NasrRetired IT Professional

Commented:
Here is a way using Sum Over Group/All

Almost identical report inserted as a subreport,, Visible=no,  just to make the total available to the main report..
Run b_report_total_per_group_in_header report.
b_report_total_per_group_in_header2 is the subreport.
report-per-group-total-in-header.accdb
Robert SchumannFacilities Planner

Author

Commented:
Big thank you to all.  hnasr - wouldn't have gotten that!  Pat and Dale what you gave me kind of worked.  Adding and referencing other text fields did the trick.  

Thank you all..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial