How do I sum multiple sums?

[RobRay]

I've been searching and trying this for quite a while, but can't quite get a sum of all my sums.



I have a report with about 15 subreports, they all contain different cost fields, ie one is ActualCost, another is DiscountedCost, etc. Each cost field is summed in each subreports footer i.e. "=Sum(ActualCost)"

How can I get a Grand total of all the sums from all the subreports?
Sometimes there will be no info for a subreport, and I don't want that to screw up the formula.

I've tried things like =Sum(ActualCost)+(DiscountedCost) and =Sum(ActualCost)+Sum(DiscountedCost) But nothing like that is working for me, it just asks for an input and the diplays the random number i type or Error.


Thanks for any help!

[ajetrumpet]

I don't do many subreports, but there is more than one tool for you. the grand total box is on the main report, right? you can, technically, INNER JOIN 15 tables in one query if they're all related, but it's not practical. if so, you can query a total and reference the field, DONE.

if not, you might have to test other reference syntaxes. I do this kind of referencing often but you can try many kinds. in the control source of the box, you might try some of these:

=reports!subreport!totalbox + reports!subreport2!totalbox2, etc...

or you might just do it in code when the report is opened. you could loop them all, if the report names have a string in common, and the total boxes on all of the subs also have a string in common. It would also be much easier if these totals on all the subs have a common way to access their sources (tables or queries).

lastly, 15 subreports, IMO, is not at all close to practical. I would change the method as soon as possible, because anything structured like that is going to be a nightmare to maintain.

[RobRay]

Thanks for the suggestions Adam, I'll try them this morning.



With regards to the 15 subreports, you're probably right about that not being practical, but I just can't think of another way to do it.

Here's a description of the database I'm working on, and if you have another suggestion to get the reports I want please advise:


I have 150 Parks, each one has a bunch of different assets (15) like playgrounds, fields, buildings. Each of the assets is a table, with each asset linked to each park through a location ID.

I have to generate a report for each park, showing all the assets in said park. Subreports in one main report seem the way to go, I have a dropdown form which chooses the park, and voila the report is generated for the park I want to see.

Any ideas on how to do a report on each park more efficiently would be great, thanks!

[ajetrumpet]

again, one table for every asset violates database normalization rules. there is technically no need for that.

ideally, with your data, a developer would set it up like so:TABLE1

• locationid
• parkname
• etc...

TABLE2

• locationid
• assetid
• assetname

and then of course as optional table for details about an asset. or, the details can go in table 2. Ideally, in table 3, but for someone not interested in theory, it doesn't really matter.

And subreports are obviously not needed, unless they are necessary for formatting. For instance, if you choose a park, then open a report and all you need to show is a list of assets for that chosen park from the dropdown list in that form, the code behind the open button is simply:

docmd.openreport "reportname", acviewpreview

now, of course you have to decide on a source for the report, and that's what should be changed. the first thing that comes to mind for me is to use a data definition query to create the query source dynamically. Yes, there would be as many queries created as there are parks, but if created dynamcially, you can control it so there is only one in existence at any given time. The problem with this sort of method is that you might create an abundance of temporary memory in the program if running it for a long period of time, which is never a good thing because it's generally not destroyed until a compact is done, or the program is closed (I think).

the other option that I can think of is to change the source sql after opening a report and requery it. I haven't done that in so long, that I'm not sure whether or not the rpt has to be in design view to do it, but I don't think so.

Many, Many possibilities here!

[pkstormy]

Couldn't you create a sub-report that is independantly dependant upon a query that just sums the values you need within the table? You'd put any 'grouping' type fields within the query and then when you embed that sub-report into your other report, use any fields you need to for linking to the main report.

This is typically what I do when I need to sum values on summed fields and show those on a report. Other times I might actually store the summed value within the table (even though it breaks the 'golden' rule to never store a summed value, sometimes it's just easier to do this and this isn't a rule that can't 'ever' be broken.)

[RobRay]

Adam, that's actually similar to the way the last person in my position set up a database, but it was not liked by the users, and thus it was not used. I agree it does make more sense, but I've been told I'm constrained to keep it in this format, with tables for each asset.

Anyway, I've gotten this to work now, with help from your reference syntax, it total's 2 subreport totals:

=IIf([Bleacherssubreport].[Report]![HasData],Nz([Bleacherssubreport].[Report]![Text9],0),0)+IIf([Benchessubreport].[Report]![HasData],Nz([Benchessubreport].[Report]![Text11],0),0)


UNLESS there is no data, in which case the subreport doesn't show up, and then I get "#Error".

I thought that putting IIf and Nz would deal with that problem, but apparently not.

Pkstormy: I tried to do a query, but with 15 asset tables, it said it was too complex.

If I could just get a bit of code to total all the subreports and ignore the ones that are $0.00 or Null, I would be happy!

[pkstormy]

If I could just get a bit of code to total all the subreports and ignore the ones that are $0.00 or Null, I would be happy!

There really isn't code per-say. It's more of a technique. Have you considered writing a few append or make table queries to put the totals (or an easier dataset in which to sum) into a table? When I come across situations like you described, I'll write a few queries to delete and then append the data to a 'temporary' type of table. Then I'll put in vba code to run those queries before the code to open the report. I'll do this when I need to sum on summed values and 1 query is to complex (remember - each table you link within a query costs processing time). I'll set up a 'temporary' type table which has generic type grouping fields and/or fields to hold the total values (or again, make it so I only have to utilize 1 query to easily sum the records in the temp table after all the append queries are run) and then use that table to either link to another table for a sub-report or as the recordsource itself for a sub-report.

Otherwise, you could put some fields on a sub-report and set the controlsource value of those fields to an expression (for example) to =Reports!MyMainReportName!TotalValueField which would grab TotalValueField from the main report. You could then total all of these expression fields together. Essentially you'd have a bunch of fields where the controlsource of those fields is an expression to = a value from the main report (or another sub-report) and then summing these fields together (using the label name of the text field which has the expression to create your other total values - ie. you'd have a field to sum the other expression fields together which has a controlsource such as =[TotalValueX]+[TotalValueY]+[TotalValueZ]....) where the field named: TotalValueX on the sub-report has an expression such as: = Reports!MyMainReportName!TotalValueFieldX (or = to a value on another subreport such as = Reports!MyMainReportName!MySubReportName!TotalValu eFieldX.)

The other way you could do it is have a query which does the summing and then utilize the dlookup in an expression for the controlsource of a field on the report or sub-report (ie. =dlookup("[MyTotalSummedValue],"MySummingQuery") or with criteria =dlookup("[MyTotalSummedValue],"MySummingQuery","[SomeField] = '" & Reports!MyMainReportName!SomeField & "'") (for text criteria matches) or =dlookup("[MyTotalSummedValue],"MySummingQuery","[SomeField] = " & Reports!MyMainReportName!SomeField & "") (for integer criteria matches - note the " " versus the ' ")

One last thing to keep in mind is how many records you are totalling. If you have a large recordset you're totalling, you'll want to make sure whatever method you use, that you don't make it so the report takes 10-15 minutes to open. Using the dlookup as an expression would be the slowest method and most time-consuming to calculate. This is often why I'll use append queries to get the data to total into a temporary type table. This is often the quickest for large datasets (but keep your appending queries simple).