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)