Access Report Subgroup summing

Question

In MS Access 2010, assume a query contains the following results:

Date     Activity     Hours
1.9.     Reading      1
1.9.     Writing      2
2.9.     Reading      1
3.9.     Talking      1
4.9.     Reading      3

1.10.    Talking      2
1.10.    Writing      1
2.10.    Reading      2
3.10.    Talking      2
4.10.    Reading      1

the Report should show the sum of hours spent each month grouped by activity, something like

Month           Activity            Hours
September       Reading             5
                Writing             2
                Talking             1

October         Reading             3
                Writing             1
                Talking             4

using the wizard I mange to get a report which looks like

Month           Activity            Hours
September       Reading             1
                Reading             1
                Reading             3
                Writing             2
                Talking             1

October         Reading             2
                Reading             1
                Writing             1
                Talking             2
                Talking             2

which is nearly what I'd like to have but.. ?

I tried =sum(Hours), but then all Hours fields simply contain the total sum of all hours, and I still get several lines for the same activity.

Actually the report is more complicated. In a form prior to the report, the user can enter a date range (e.g. September 1 - October 30). The report should sum over the whole time range and not break up to months, that is:

Date range      Activity            Hours
9/1 - 10/30     Reading             8
                Writing             3
                Talking             5

Andre Andre · Accepted Answer · 2015-09-01T17:51:23

Create a query with a SQL like this:

SELECT MonthName(Month([Date])), Activity, SUM(Hours)
FROM yourTable
WHERE [Date] >= xxx AND [Date] <= yyy
GROUP BY MonthName(Month([Date])), Activity

The key element is the GROUP BY clause, it sums the Hours per Month + Activity.

Then base your report on that query.

BTW, it is not a good idea to call a column "Date", it's a reserved word in Access.

Access Report Subgroup summing

1 Answers