Renewing member database design question.

[Dennis]

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

 

[Allen Browne]

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.

 

[Dennis]

Allen,

Can do the two tables. No worries.


Your comment:
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.)

Response: Very elegant. That solves a lot of issues.


Your comment:
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.

Response: I’ve already tried this approach. It was my understanding that a
Group By and Max(RenewalDate) results in a read only query. One of my
requirements was the query support data entry so I can create a data entry
form around it. Sorry if I was not clear about that earlier. Since
membership renewals arrive in “bunchesâ€, I want to have the continuous form
entry screen. This screen will have a comb box for member name, and then
fields that allow me to enter the renewal information.

Using the Max() does solve the function of connecting the Client table to
the Payment table without having to worry about the payment term, but it
leaves me without the data entry capability I need.

That is why I was using the

SELECT tblMember.AcctNo, …..
FROM tblRenew RIGHT JOIN tblMailingList ON tblRenew.AcctNo = tblMember.AcctNo
WHERE (((tblRenew.MemYear)=Year(Date())));

This join provided me with a data entry enabled query that I could use as a
source for a data entry form, but then I have the issues of Life versus
Annual.

I could modify my data entry form to post the key to the last payment
transaction to the Client table as a foreign key and that would give me a
direct link. I would have both a data entry capable query and resolves the
Life and Annual issues.



Thanks,

Dennis

 

[Allen Browne]

Yes: a GROUP BY query will give you a read-only result.

You could work around that with a DMax() expression, either in your source
query or in the Control Source of a text box on your form:
=DMax("DateAdd(""yyyy"", [Years], [FromDate])", "PaymentsTable",
"ClientID = " & Nz([ClientID],0)

I would try to resist the temptation to store this value as a field in the
Person table, as this violates basic normalization rules ("Don't store
dependent data.")

 

[Dennis]

Allen,


You could work around that with a DMax() expression, either in your source
query or in the Control Source of a text box on your form:
=DMax("DateAdd(""yyyy"", [Years], [FromDate])", "PaymentsTable",
"ClientID = " & Nz([ClientID],0)

Ok, I’ve not used this before so I’m going to have to play with it.




Your comment: I would try to resist the temptation to store this value as a
field in the Person table, as this violates basic normalization rules ("Don't
store dependent data.")

My Response: To which value are you referring – The current renewal record
key or the current year? The current year is a calculate field based upon
the system’s current year.


Dennis

 

[Fred]

Hi Dennis,

I wrote and run membership databases for 5 organizations, varying from 12 to
600 members.

Your structure should also be determined by your needs and goals. For
example, yours is oriented around databasing the renewal as an entity, and
recording the details of such. In my case, in the largest organizaiton, the
treasurer does and records all of that separately.

For my objectives, I have settled on what some would call an unnormalized
structure of one field in the "people" table for each year. I have fields
for 5-10 years into the future, and, each year, I take 30 seconds to write a
simple view query to mark life members (that haven't died etc.) as members
for the new year. The biggest advantage is that it's about 20 easier /
simpler to query based on membership history. For example, current members
who were also members in 1955 and 1965.

One of the organizations has been in existence since 1937, and so, after
about another 150 years I'll have to restructure that one.

 

[Dennis]

Fred,

It sound interesting, but I'm don't quite understand how you did it. I'm
sorry, but could you provide a little more detail? I am all for simply since
this only 400 members.

Since you brought up deceased members, I have a date of death field on the
member rcd, so I know who had died.


Thanks for your assitance.

Dennis

 

[Fred]

Hello Dennis,

I'd be happy to. There's no claim that it's what you should do, my only
claim is that it's good for my "600 member" situation.

First, I am the secretary of that organizaiton, and there is a good
Treasurer. And so the treasurer records all payments (actually in a General
Ledger Access application that I wrote for him) and he gives me list of who
paid, organized so that he tells me "once and only once" for each payment.

I also issue (mail merged from Access) "invoice/update" forms once a year
which print out all of their current information and asks them to mark it up
with updates. When these come in with money, the treasurer gives me the form
instead of the "notice"

Membership is to individuals, although in some cases I make badges for their
family members (more on that later)
- - - - -

So my DB (in the areas that you are discussing) is basically one big table
of people who are members or whoever were members. It has about 20 fields of
"one to one" type information for that person. (Name, address, email,
land-line phone, cell phone etc.. There is also a field for "LifeMember", a
"Y" in that field = yes,

BTW, when other persons in the organization have databases that are well
run enough to really have and respect a primary key (such as work
scheduling) also have that as a field so that I can either link and use their
data, or export sets of data to them that is helpful to them, including their
PK. If I were a DB architecture structural purist, I would dictate that
they use the PK of the organizations main databases, but I decided that that
would not be a good idea because it would make those valuable volunteer's
jobs harder.

And I have a column that indicates membership status for each year that the
organization has been in existence. A "Y" in that field = is/was a member in
that year, a null in that field = not a member in that year. The
organization started in 1937. So I have about 78 fields indicating
membership status for each year from 1937 until 2015. (e.g. field names:
"mem37", "mem38" .......mem09, mem10). I have been runniign this databased
(moved from Dbase to Access) for 22 years. Prior to that, I am slowly
entering from onld membership lists, etc. for historical purposes. This a a
rare case (and yes there are some) where a fully normalized database would be
less optimal for the needs. I know exactly howo I would normalize this
aspect, I know it well enough to not do it.



When I hear that someone has dies, I enter that in the "notes" field.

And then end of the year (using this year as an example) I write a query
which shows only life members who were member in 2009, and which shows their
first name, last name, notes field and the "mem10" field and, unless they
died, I check them off for 1020 membership. If we had more than 20 life
members, I would further utomate this, but I don't.

- - -



I mentioned that I only have one main table in the areas that you discussed.
I have several more linked "one to many" tables for other items such as:

- For issuing "family member" version of membership badges, when ordered
(the badges are a color Access report)
- recording instances of dontations
- recording instances of a person serving in elected positions

This organization is big on history.

- - - -

I debate with my son who runs 100,000,000+ record databases. The rules that
should be categorically followed for such a databases would be be a bad idea
for someone who has decided that they want to use Access for their grocery
list.