Two tables:
- a Client table, containing only details about the person (nothing about
membership or renewals.)
- a Payments table, containing information about payments received
(including membership payments.)

The payments table would contain fields that indicate the amount paid, the
membership period this starts from, and the number of years it covers. You
can indicate that a life membership covers (say) 99 years. The next
membership payment is due:
RenewDate: DateAdd("yyyy", [Years], [FromDate])
where Years is the number of years they paid, and FromDate is the date they
joined (or renewed.)

So, you type an expression like that into a query.
Group By the member.
Choose the Max of RenewDate.
That's when they are due again.

You will also want to add an Inactive (Yes/No) field to the Client table,
and exclude those from this query, so you don't send renewal notices out to
dead people.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Dennis" wrote in message
...
Hi,


1. Background:

I have a member database for a local community club. I’m in the process
of
adding membership renewal processing to the module. There are two types
of
membership; Life Time and Annual.

The Life members pay a one time fee and are members for life. The Annual
members pay a membership fee once a year. The renewal drive starts in
November. In October, when we print the November newsletter, we include a
reminder notice in the monthly news letter that is mailed each member.
Members will then pay their renewal by either check or cash.

I want to start tracking the renewal history, which I do not do right now.
Currently, I just have the member ship term (annual or life) and current
expiration date in the master rcd.


2. Existing DB:

I have a Member Master table as follows:
Tbl tblMember
Key: AcctNo – Automatically assign number.
Fld: Name
Address
Etc.
Membership Type (Annual or Life)
Expiration Date (Annual terms expire on 12-31-current year
Life terms expired on 12-31-2099)


3. Proposed new DB
Tbl tblRenew
Key: RenewalID: Automatically assign number.
Fld: AcctNo
New / Renew Flag N = New member, R = Renewing member.
MemYear = Calendar year for membership
(ie. 2009, 2010, etc.)
DtPaid = Date the member paid their dues.
PaidBy = How did member paid their dues (check or cash)
AmtPaid = How much they paid for membership.
(Can chg each year)
RenPrtDt = The date the membership Dues & Remittance
form was sent to National.
DtCardRcvd = The date the card was received back from
National membership.
DateExpires = The date the membership expires
(end of current year of 12-31-2099)


I have a query that joins the tblMember to tblRenewal for the current term
as followings:

SELECT tblMember.AcctNo, tblMember.LastName, tblRenew.NewRenew,
tblRenew.MemYear, tblRenew.PaidBy, tblRenew.PaidBy, tblRenew.AmtPaid,
tblRenew.RenPrtDt,
FROM tblRenew RIGHT JOIN tblMailingList ON tblRenew.AcctNo =
tblMember.AcctNo
WHERE (((tblRenew.MemYear)=Year(Date())));


I also have another query that joins the tblMember to tblRenew for all of
the renewal terms.

4. Issues / Questions.

A. Any suggestions on how to deal with Life Member? About 50% of the 400
members are life members. Should I just run an update query once a year
to
create a copy a renewal record for each Life members? Or is there a
better
way to deal with Life members?

B. As far as regular members to, I was planning to just have the user
create a new record at data entry time. I really don’t want to copy 2009
to
2010 for renewing member because they I would have to delete those 2009
member who did not renew. Any suggestion on this approach?

C. I originally joined the tblMember and tblRenew using a Group By and
Max(CurrYr). However, this approach left me with a read only query, which
I
did not want. I want to end up with a query where I can change that data
because it will be used for the data entry form. The query I have above
is
input enabled.

Anybody have a better way to join the two tables?

D. I know about the Allen Browne multi-tier approach and for simplicity,
I’ve chosen not to implement a Family / Family Member table structure at
this
time. I only have 400 members, of which I have maybe 30 people in a joint
address.

E. Does anyone have any other suggestions?


Thanks,

Dennis