ok so an alternative way consider doing it in Access, rather than using SQL. it may even be more efficient and quicker than using SQL here and there.
open the report in design mode
add a control in each footer as required, with label if needed
switch to the code window
declare a variable(s) to hold the sum for each required grouping
set each variable to 0 in each relevant group header
add 1 to each group variable in the forms detail event
assign the relevant value to the relevant group footer
..it does mean you have to be prepared to dive into a bit of code
it does mean you have to be prepared to have a play with report design structure
it does mean you have have made a start form letting Access do everything to you and do more for yourself.
In this case its a fairly trivial example, but manipulating code behind the access UI is a powerful technique