Access 2013 - How to sum (on group footer) a field that is based on a complex value (from multiple embedded aggregate total queries)

Hello - I have an Access 2013 report based on a collection of queries with aggregate sums, all rolled up and connected to the final query. These sums also have field calculations before they are summed. By the time I get to the report, the report can't seem to handle any of the options in summing the fields. When using Sum(Field), ether get an error:

 "The expression is typed incorrectly, or it's too complicated to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

Its not really practical to try and directly reference the root fields originally used in the base queries, as they are too deep into the nesting. I would probably have to rewrite all of the queries, which I would like to avoid. The other approach is possibly using a DSum tying into the key field on the report, but that's not the most elegant solution.

I would like to know if there is any other work around for dealing with these more complicated fields to simply get them to sum on the report footer. Thanks.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Without having Access to your database and report, is hard to say what the possible solution might be...

Can you post a simple, sample database that exhibits this issue?
Then post a clear, graphical example of the exact output you are seeking.
I can't be sure based on your description so I don't know if this is what you are doing but I'll throw it out just in case.

In a report, you cannot reference calculated controls from other sections.  You need to repeat the calculation.  So, in the detail section you have ControlX which has a ControlSource of:
= FldA + FldB
You want to sum this in the report footer so you try the obvious:
But that throws an error.  The solution is to repeat the calculation:
=Sum(FldA + FldB)
Eric ShermanAccountant/DeveloperCommented:
>>>>The other approach is possibly using a DSum tying into the key field on the report, but that's not the most elegant solution. <<<<<

Like the other experts ... without seeing your report it is hard to give you a practical approach.  However, when I have been faced with a similar situation that you described ... I used the DSum() method simply because you can include the specific criteria needed for the sum.  May not be elegant but it works.

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Jeffrey CoachmanMIS LiasonCommented:
You may even be able to load the query results into a temp table to get what you need.

But again, we are getting ahead of ourselves here.
Perhaps the report could even be simplified,
...In any event, we need to see a sample db of what we are working with here.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
maknitCEOAuthor Commented:
To all who have posted - thank you for the great feedback. To address the more salient points:

-It's not really feasible posting a test database, as there are so many tables, nested queries, and volume that all contributes to duplicating this issue.

-I am aware of using the base fields in a calculated control for summing on a report, but as mentioned, the base fields are no longer in the picture, since they are part of a query that is deeply nested. If I referenced those fields, the Access report won't find them anywhere, since only the aggregated calculated fields are what's available.

-However, all of these points and the feedback considered, I believe the most realistic solution is using either the Dsum function or a temp table as mentioned.

Great feedback - and thank you. I'm going to close this out.
maknitCEOAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for maknit's comment #a40881871

for the following reason:

Great solutions - realistic given the constraints.
maknitCEOAuthor Commented:
Great feedback - these are the likely solutions that will be used.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.