How do I count groups within a group?

M

murrwheel

My report groups by City and client ID. There are several clients for each
city and each client may have one or more records which are easy to count and
total, but I need to count the number of clients (client groups) for each
city.
 
D

Duane Hookom

Since this is a report news group, create sorting and grouping levels of
first City and then ClientID. Add a text box to the ClientID header section:
Name: txtCountClient
Control Source: =1
Running Sum: Over Group
Now add a text box in the City footer section:
Control Source: =txtCountClient
 
M

murrwheel

Fantastic! This approach works fine. I spent at least 8-10 hours trying to
get to this point. Your help very much appreciated.
One problem remains, in trying to total the clients for the entire report.
txtCountClient in the report footer results in 1. I tried the same approach
with adding a text box to the report header which didn't work. Also tried
=Sum([txtCountClient]). Another failure. I tried all three options of
Running Sum (No, Over Group,Over All) with each approach which did not help.
I must be missing something.
 
D

Duane Hookom

If your report total is the sum of your other totals, just duplicate your
previous controls but set a running sum over report on the new text box in
the City footer section. Reference this control's name in your text box in
the report footer.

--
Duane Hookom
MS Access MVP
--

murrwheel said:
Fantastic! This approach works fine. I spent at least 8-10 hours trying
to
get to this point. Your help very much appreciated.
One problem remains, in trying to total the clients for the entire report.
txtCountClient in the report footer results in 1. I tried the same
approach
with adding a text box to the report header which didn't work. Also tried
=Sum([txtCountClient]). Another failure. I tried all three options of
Running Sum (No, Over Group,Over All) with each approach which did not
help.
I must be missing something.

Duane Hookom said:
Since this is a report news group, create sorting and grouping levels of
first City and then ClientID. Add a text box to the ClientID header
section:
Name: txtCountClient
Control Source: =1
Running Sum: Over Group
Now add a text box in the City footer section:
Control Source: =txtCountClient
Click to expand...
Click to expand...
 
M

murrwheel

Works like a charm. Thank you, thank you, thank you. I was at a dead end.

Bill LeValley
 
Top