How do I count total clubs and activities?

[rocklee]

I have a club and activities tables which I need to tally in a report for the total number of clubs to be listed on top of the page and the list of these clubs with their activities (including the total activities).

I can list the total activities per club but I can't seem to get the total clubs.

I tried using Group & Sort which gives me the running activities for each clubs (Group on Club_Name, Group on Activity Name). The field used to count the total activities per club is :

=Count([Club].[Club_ID])

Can anyone advice?

 

[khawar]

On report header use this

=count([Club_Id])

 

[Galaxiom]

khawar's suggestion will still count the number of records even if they are repeated as it is equivalent to the OP's original attempt.

It is easy if you just want the total number of clubs in your database.
=DCount("*","tablename")

It is trickier if you want to count the number included in the actual report.
If your report is based on a saved query you could use the ECount function on that query.
http://allenbrowne.com/ser-66.html

Otherwise get a list of the clubs included in the query with:
Select DISTINCT [Club] from queryname


Create a subform in the header of the main report with this as its Record Source. (Or you could use a seperate version of the main report group by query with just the clubname.) You might even like to display this list at the beginning of the report by using the datasheet view of the subform. Otherwise you can set the subform control to not visible.

Count the records in a textbox in the footer of the subform using =Count(ClubID)

You can then transfer this count to the main report by refering to it in the control source of the main form header textbox.