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.
"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.
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:
=Sum(ControlX)
But that throws an error. The solution is to repeat the calculation:
=Sum(FldA + FldB)
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:
=Sum(ControlX)
But that throws an error. The solution is to repeat the calculation:
=Sum(FldA + FldB)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
-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.
ASKER
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.
Accepted answer: 0 points for maknit's comment #a40881871
for the following reason:
Great solutions - realistic given the constraints.
ASKER
Great feedback - these are the likely solutions that will be used.
Can you post a simple, sample database that exhibits this issue?
Then post a clear, graphical example of the exact output you are seeking.