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