I have been running electronic databases for organizations for 21 years, and
currently do it for six. Also have had to deal with every issue that you
describe plus other more complex ones. Sub-members to members, an
organizaiton with current members who have been in for 60 years and I know
their membership status for each of those 60 years) In my opinion (and this
is where those immersed in DB design theory may disagree with those that have
really been doing this for small / medium sized volunteer organizations):

The design starts with asking these questions:

1. What are the main uses of this information system? Record current
status? Create reports? Long term / historical record keeping? Print
Badges? Drive the newsletter mailing?

2. In the context of your answers to #1, and, yes, knowing all of those
variations that you talk about, is there sort of one core status at the
center of all of these? Like (being deliberately vague) "member", or "gets
the newsletter".

3. How big is the organization?

With most organizations, the answer to #1 is "many of the above" and the
answer to #2 is "yes, it's "member" (of some type)", and to #3 it's small or
medium sized volunteer organizaiton. For them, it's one big table for all
of the information that you describe. Make a field for each year (than
needs to be tracked" for that main status (e.g. "member" , "paid"). And
yes, this means that each year you will be making minor changes to
everything. And a field for each of those statuses that you describe.
Those that are mutually exclusive can be combined.

If you want to record more extensive information (other than that main
status through the years) on individual, do that through linked tables.
For example, a "history" table which records instances of them holding an
office over the last 60 years, or a history of donations etc..

If the answer to #2 is "no" then what you really have is separate
organizations / tables which merely draw from a common list of people. In
that case, makke one "people" table (with numbers for them) which has their
non-organization data (name, address etc.) with a primary key(e.g. "persnum")
and a separate table for each organization which links to / draws from the
people table. And that organization table has fields for everything that it
needs to track.

Fred


"Lisa - NH" wrote:

Hi. I have placed a few other posts in the new user forum and have received
some help. I've been using Excel. I jumped into Access before doing
reading/research on things. Planning on starting again. Please bear with me
as I tend to be very detailed. In responding please keep in mind that I'm
completely new to Access and databases in general and need "dummy"
instructions.

The database will contain the membership for our American Legion post. This
includes the Legion itself, the Auxiliary and the Sons along with a list of
those we send courtesy copies of our monthly newsletter to. I won't give
full details as to the changes that have been made or why. Right now all the
members are in one table (including those we send the courtesy copies to).

I know the table is a mess as it has too many check boxes (1 for each
"group", there are 4), 1 for each type of membership (there are 5) and one
for the mailing of the newsletter.

I plan on breaking into seperate tables and need some detailed help. Here's
my thinking and questions.

Member Table: will include
name (first, middle, last, suffix)
address
city
state
zip
phone
email
birthdate
notes
The primary key will be an auto-id field. I do have a member id field but
it is not possible to use this as a primary key because #1 - when a new
member joins they aren't given a member number until they are processed
through Department & then National. We get supplemental rosters every 3
months with that information. and #2 - the people who get courtesy copies
aren't members and therefore don't have a member id.

Group Table: will include
Auxiliary
Legion
Sons
Courtesy

Membership Type: will include
Honorary Life
Paid Life (those that have paid one fee for lifetime membership)
50+ (those with 50+ years are paid for, mostly in the Legion roster)
70+/20+ (those 70 & up with 20+ years in the Auxiliary, we pay for)
Juniors (only applies to Auxiliary)
(Note: Yes I know that you aren't supposed to use spaces or characters in
the field names and I did read somewhere that you aren't supposed to start a
field title with a number.)

Question 1: The Member ID field. I was told that it should be in a
seperate table because we have certain members that are "dual members". Some
are dual Legion - SAL and some are dual Legion - Auxiliary. So they would
have more than one member number. Exactly how do I do this without screwing
up which number goes with which member?

2. How do I keep track of the paid information? In the original Excel file
we were originally keeping paid info back to 2005 with each year it it's own
column. That was getting a little annoying as the worksheet was just getting
way to wide and we just started keeping the current year and the previous
year. When I first setup Access, I just put in a column for the current year
(named it 2008). I realized that wasn't going to work as we'd have to update
every year, so I changed it to just paid. Thinking about it, I do think it
would be best to have a way to keep track of at least the current year &
previous year. Should I do a seperate table with this info? Is my thinking
correct that if we did that, each year we'd add a new year and we could keep
a history of the old years? Could this info be put into the table with the
member numbers? Details please.

3. We do mail a newsletter out monthly. We only send one copy to each
address. Now there are those who don't wish to receive a copy at all and
there are those who receive it via E-mail. Currently I have a simple yes/no
check box (but that doesn't help in figuring out who gets it via e-mail (I
can't just look at my e-mail field because not everyone that I have an e-mail
for, gets the newsletter via e-mail.) Where do I put this?

Any and all suggestions are appeciated.
Lisa