Do you want percent increase from one year to the next?
I would be inclinced to create a second table to store the calculated data in, and run a process on your initial data to calculate the values.
The psuedo code would be:
Set warnings to false
DoCmd delete query on second table
Set warnings to true
Open connection to database
Open recordset on this first query you have done that totals the data.
Do until rs.eof
Open recordset on second table for updating.
Pass data from first record to second table
(do not add percent change)
Store the data from record in variables.
iEventsCur - current year event value
iEventLast - previous year event value
iAttendCur - etc
iAttendLast - etc
If recordset <> first year (1997) then
Calculation to compare data from current record in loop to last record in variable
rs1.AddNew
rs1("Year") = rs("Year")
rs1("Events") = rs("Events")
rs1("Attend") = rs("Attend")
rs1("EventChange") = lgEventCalc
rs1("AttendChange") = lgAttendCalc
rs1.Update
End If
Pass current records values to Last Year's variables for comparison in next loop.
iEventLast = iEventCur
iAttendLast = iAttendCur
rs.MoveNext
Loop
'----------------
Then run your report based on this second table.
It seems like a longer way around perhaps, but it is more robust than detail section calculations, global variables, and modules. Since the code is attached to the button running the report, it is dynamic, and less subject to breakage.
Does this help? I can help with the actual code if you have table structures, field names, etc.
HTH
mmcdonal
|
