return all totals for each month
I haven't been able to achieve getting a total or average of all amounts of each month.
What I would like to report is:
January 15.50 (22.00)
February 75.67 (70.23)
where the first number is the sum of all transactions for a given month in a year (I've done that), and the second number is a total, or actually an average of all January's etc. of all years, such that it is easy to see if you are higher or lower than average. It would also be easy enough to make it into a percentage. I thought I could use a self-join:
Main Table-----X----- Main.All Main.Month
So, .All would return all records regardless of found set, and month would return just the totals for the month. But since it returns the same number all the time, I need a way to specify what the current month is on the report.
The report is based on the Main Table and is generally filtered to show just one year at a time. All seems to work except tallying up the all-time amounts.
Hope this makes sense, and I've given enough info.