Calculate variance in group footer

[cmk7471]

I have a report that is grouped by publication and compares subscriptions
this year to last year. I'd like to add a variance showing the difference
between this year and last year in the group footer. Below is a
simplification of what the report looks like. I need to calculate where I
have ???. How can I do this?


Pub 1
Date Accts Copies
2009 ### ###
2008 ### ###
---------------------------------
variance ??? ???

Pub 2
Date Accts Copies
2009 ### ###
2008 ### ###

 

[Duane Hookom]

It would have helped if you had provided actual numbers with the desired
variances.
I believe you could use an expression like:

=Sum(Abs(Year(Date()) = [DateYear]) * Accts) - Sum(Abs(Year(Date())-1 =
[DateYear]) * Accts)

 

[Jeff Boyce]

Just in case this wasn't implied...

The term "variance" means one thing in relation to accounting (roughly
translated = "difference"), and something else altogether in relation to
statistics (a measure of central tendancy). Can we assume you mean
"difference"?

One way (perhaps too slow, but worth a check) would be to use the DSum()
function twice, once with the current year and once with the previous year,
as a selection criterion. Check Access HELP for the exact syntax required.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[cmk7471]

Finding the difference between the 2 DSUM formulas worked! Thank you so much!