1. ## Counting Months (2003)

Hello,

I have a table of data that shows members and their start and term date. I am trying to compute membership per month. So if a member has a start date in jan 07 and May 07 term date then has has membership in 5 months. If the next member has membership in April and May then the total membership for April and May would be 2 based on the 2 members. Then I will then need to count the number of months for all the members. What would be the best way to do this?

Ben

2. ## Re: Counting Months (2003)

To count the number of months per member:

Create a query based on your table.
Add the member ID (and name if desired) to the query grid.
Enter the following expression in the first empty column:

NumberOfMonths: DateDiff("m",[StartDate],[TermDate])+1

replacing StartDate and EndDate with the actual field names.

3. ## Re: Counting Months (2003)

To count the number of members per month, I'd create a table with a date/time field TheMonth and populate it with a record for the first of each month you're interested in, e.g. 1/1/2007, 2/1/2007, ..., 12/1/2007. (You could create a list of dates in Excel, where this is very simple, then copy and paste into your Access table). Set the Format property for the field to mmm yy or something similar.

Create a query based on your table and on the table listing the months. Don't join the tables.
Select View | Totals to turn the query into a Totals query.
Add the TheMonth field from the date table to the query grid. Leave the Total option as Group By.
Add the field TheMonth again, to the second column. Set the Total option to Where (this will clear the Show check box). Set the Criteria for this column to

Between DateSerial(Year([StartDate]),Month([StartDate]),1) And DateSerial(Year([TermDate]),Month([TermDate]),1))

replacing StartDate and TermDate with the actual field names.
Add the field TheMonth a third time. Set the Total option to Count.

This query will return a list of months and the number of members for each month.

4. ## Re: Counting Months (2003)

For the year month excel table that I bring into access,I will also add region. Will this still work?

5. ## Re: Counting Months (2003)

That depends on what you want to do.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•