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)

Who is Participating?
Dale FyeConnect With a Mentor Commented:
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.
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!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.