Get Sums for 2 DCount fields on report

[laskowv]

I have a report that is grouped by SECTIONID, and within those sections are
societies with SocietyID as the key. I was shown how to use the DCount
function in my form to show the number of chapters and members within each
Society by using the expressions below in two text boxes:

=DCount("*","Society Active Chapters","SocietyID =" & [SocietyID])
=DCount("*","Society Active Members","SocietyID =" & [SocietyID])

I have put these expressions in the "Detail" section of my report and would
now like a "grand total" or SUM of these counts at the SECTION level and the
REPORT Footer level. I have seen some notes on DSum but am not sure this is
what I need. Both of the above expressions are queries. Could someone
please show me how to get the totals?

Thanks..Valerie

 

[Allen Browne]

You could put another text box on the report, give it the same Control
Source, and set its Running Sum property to Over Group. It would then
accumulate the total until you reach the next section header/footer.

However, there's probably a better way to approach this, by doing it in the
query that feeds the report:

1. Create a new query using [Society Active Chapters] as a source table.

2. Depress the Totals button on the toolbar. (Upper case Sigma icon.)
Access adds a Total row to the query design grid.

3. In the Total row under the SocietyID field, choose Group By.

4. In the Total row under the primary key field, choose Count.

This query gives you one record for each SocietyID, and a count beside each
one. Save this query, and you can now use it as a source "table" for the
query that you use for your report. Join them on SocietyID.

Now you have the CountOfSocietyID field in your report. You can display it
without a DSum() expression. And you can easily show it in the group footer
or in the report footer just by adding a text box with Control Source like
this:
=Sum([CountOfSocietyID])

You will find that is *much* more efficient than using DSum().