Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #4
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •