Prev: Part and Product Database Help - replied by Losers R US
Next: AYS Link broken?
From: Fred on 1 Mar 2010 08:52
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. :-)





From: Dennis on 1 Mar 2010 11:10

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
From: Fred on 1 Mar 2010 13:56
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.




First  |  Prev  | 
Pages: 1 2
Prev: Part and Product Database Help - replied by Losers R US
Next: AYS Link broken?