asked on

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.

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

Marc

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marcchevalier

ASKER

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