Counting Records in a Report

[RonMcIntire]

All:

I have a report (chapter directory) with a list of chapter officers for each chapter--one chapter per page--in my organization.

The chapter officers (Pres. VP, Treas, etc.) and chapter names etc., are contained in a subreport.

How do I count the number of memberID's in a chapter then display that count on that chapter page so that I have a chapter directory listing the chapter officers and the total number of members in that chapter.

I can count the total members in all the chapters, and
I can count the number of officers in each chapter with a query but outside of that, I'm lost.

Any help will be appreciated.


Ron

 

[dhookom]

By "chapter names" do you mean the names of members of the chapter or the name of the chapter.

Do you mind sharing the names of the significant tables and fields?

Does the main report contain only one record per chapter? Are the officers also in the member table? Do you want to only count non-officer members or all members?

Duane
Hook'D on Access
MS Access MVP

 

[RonMcIntire]

1. Each of our chapters has both a number like "A-000" and a name like "Kansas City Chapter". The chapter members each have a 6-digit member number along with their name.

2. All the tables I use come to me from our parent organization (Society) and I modify them with queries for use in the database. The two significant tables for the report are:

qryChapters
Chapter Name
ChapterNo
City
ST
ZIP
etc.

qryChapterMbrs (This qry can be used for counting by counting all the like chapter numbers. Although it contains no records or member numbers, there is one record per member in the chapter. Because some member expiration dates fall outside the current year, I must filter for the current year. Thus the count is based on one record per membership expiring in 2009 or later.)
ChapterNo
Member Expiration Date


The subform table contains:
MemberID name, address, city, st, zip and officer position.

3. The main report contains only one chapter record per report page but each page contains all the chapter officers in that chapter. Yes, only one record per chapter.

Yes, the officers are drawn from the member table.

I need to to count all the members in each chapter including officers and non-offers.

For Example:

Chapter A-000 Frank Thorne chapter has 63 members
Chapter A-048 Kansas City Chapter has 136 members
etc.

Hope I've included enough information.

Ron

 

[dhookom]

You can change the main report's record source to something like:

SELECT c.*, Count(*) As ChaptMembers
FROM qryChapters c JOIN qryChapterMbrs m on c.ChapterNo = m.ChapterNo
WHERE [Member Expiration Date] >= #1/1/2009#
GROUP BY [Chapter Name], c.ChapterNo, c.City, c.ST, c.ZIP, c.etc;

This should allow you to add a text box with a control source of Chaptmembers.

Duane
Hook'D on Access
MS Access MVP

 

[RonMcIntire]

Thanks for your response. It took a little bit to figure out but I understand now.

Thanks again. This should work.

Ron