Trouble Summarizing in Group Footer

[Millerguitarworks]

I tried to explain this in an earlier post, but I think I made it too complicated, so I'll try to ask in a more simple manner:



I have 3 related tables: CLIENT, PROGRAM and SUBSCRIPTIONS (the junction table). I created a report grouped first by PROGRAM, then by CLIENT with SUBSCRIPTIONS in the detail section. Each client can have more than 1 SubscriptionID for the PROGRAM. I am trying to calculate the number of CLIENTS in the program, and every "count" or "sum" expression I try returns a count of the SUBSCRIPTIONS, not a count of the CLIENT.

For example, for a PROGRAM there are 3 CLIENTS, and 4 SUBSCRIPTIONS. That means one of the clients has 2 SUBSCRIPTIONS. When I create the field in the PROGRAM footer and enter the expression:

=Count([ClientID])

With ClientID being the foreign key in the SUBSCRIPTIONS table.
It always returns a value of 4. When I view the report, there clearly are only 3 CLIENTS. What gives???

[Graysworld]

I have the same problem. Did you find a solution? Seems the community doesn't tend to answer this type of question.

[Graysworld]

It always returns a value of 4. When I view the report, there clearly are only 3 CLIENTS.

Ok, I've found the solution to my problem, and I think it might solve yours too. Sorry if it's too late. Follow the link:
http://www.microsoft.com/office/comm...F-383FFF305F63

My post was here:
http://www.utteraccess.com/forums/sh...b=5&o=&fpart=1

I think you will find that if you look at the record source (the query supplying the records) you will find there are 4 records, with one client repeated once. So when you count the number of clients (or programs), it will return 4. I think you will need to insert a text box in the ClientID header with ...
Name: txtCountOfClients
ControlSource: =count([clientID])
Visible: No
RunningSum: overall

Then in the report footer...
ControlSource: =txtCountOfClients

If this doesn't work for your case, try adding the txtCountOfClients to the ProgramID header.