Calculate a group footer field based on other totals in the footer.

I have an Access report that compares sales activity between teams every quarter. Here is a simplified sample version of the report where team sales are accumulated in a group footer for Last Quarter (col 1) and This Quarter (Col 2). Column 3 displays the percent change from col 1 to col 2. I know of no aggregate function to calculate a total percentage for the entire group and that is what I need in the yellow area. Unless someone has a better idea, I am looking for a way to reference the column totals in the footer to calculate the change (col 2 total / (col 1 total - col 2 total)) in, say, the BeforeUpdate event of an unbound field and display it (-24.60%) in the footer.

Sample report page
I am using Access 2007 (oldest common version among users)

Marc
marcchevalierAsked:
Who is Participating?

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

x
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.

Dale FyeOwner, Developing Solutions LLCCommented:
control Source: =(Sum([This Q]) - Sum([Last Q])/Sum([Last Q])

Assuming your calculations are (This - Last)/Last, however, if Sum([Last Q]) = 0 this will error out.
0

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
marcchevalierAuthor Commented:
Dale: So the aggregate functions themselves can appear in the Control Source.

Excellent! That works. Sorry about the confusion in my narrative of the calculation. You are correct about the order and I have wrapped the denominator with the NZ function:

Control Source: = (Sum([ThisQ]) - Sum([LastQ])) / Nz(Sum([LastQ]),1)

Odds of a 0 value are minute, but not impossible, so I will test this with a forced zero denominator to make sure this is not an Nz failure exception.

Yours is all the solution I need. THANKS!

Marc
0
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.