You can do this in 3 steps:
a) Create a query that gives a total for each month.
b) Use that query to create a crosstab to place the years beside each other.
c) Create a report, and use Running Sum to give you the Year-To-Date
figures.
The steps are:
1. Create a query using your table.
2. Depress the Total button on the toolbar.
Access adds a Total row to the query design grid.
3. Type this into a fresh column in the Field row:
TheYear: Year([SaleDate])
Replace SaleDate with the name of your date field.
Accept Group By under this field.
4. In the next column, type this into the Field row:
TheMonth: Month([SaleDate])
Again, use your field name, and accept Group By.
5. In the next column, choose your Amount field.
In the Total row under this field, choose Sum.
6. Save the query with a name such as qryByMonth.
Test: you should see a figure for each month.
Close the query.
7. Create a new query. Use qryByMonth as the input table.
8. Change it to a Crosstab query (Crosstab on Query menu.)
Access adds Crosstab and Total rows to the design grid.
9. Drag TheMonth into the grid.
In the Total row, accept Group By.
In the Crosstab row, choose Row Heading.
10. Drag TheYear into the grid.
In the Total row, accept Group By.
In the Crosstab row, choose Column Heading.
11. Drag SumOfAmount into the grid.
In the Total for, choose Sum.
In the Crosstab row, choose Value.
12. Test: you should now see month-by-month results beside each other.
13. Open the Properties of the query.
Beside the Column Headings property, enter the years you will ever need to
compare, e.g.:
2005, 2006, 2007, 2008, 2009, 2010
14. Save the query with a name such as qxtabByMonth.
15. Create a report using qxtabByMonth as the source.
16. Place the text boxes across the Detail section like this:
[TheMonth] [2005] [2006] [2007] [2008] [2009] [2010]
17. Set the Running Sum property of these text boxes to:
Over Group
The report now gives you a row for each month, a column for each year, and
the Year-To-Date totals at the intersection point.
