Help - Membership database

[Lisa - NH]

Hi. Bear with me, I'm going to be as detailed as I can be. I hope people are
willing to take the time to read and offer help.

I'm very new to Access. (I did buy the 2007 version.) I'm still using Excel
while getting everything set up in Access. I currently do membership for our
Legion Auxiliary and I maintain the membership rosters for the Legion & Sons
as I print labels each month for our newsletter. In Excel I had worksheets
for each group along with some specialized information I wanted to keep track
of. Only certain members receive the newsletter and our post office requires
they be sorted by zip code in 2 different boxes so I had those put into 2
seperate worksheets as well.

In setting up the Access file, I decided to keep the 3 groups in 3 seperate
tables...just because I didn't know what else to do. I have things set up a
little different already in that I have added information that wasn't in the
original setup. There are currently 19 fields (I won't give all the
details). We had someone who does a lot of work in Access 2003 (but not at
all familiar with the 2007 interface) look at the setup and he said that we
should put all 3 rosters into one table instead of 3 and then have colums at
the end of each that specify what membership group they are in. He said you
then setup a query so that you can view membership for just one group at a
time. Now he has offered to do all this work for me but I want to learn as
much as I can on my own before I "give up".

I did make all the fields match in each table even if that particular field
wasn't being used by the other group. Example for the Auxiliary we have a
birthdate field because we pay dues for members over a certain age with a
certain number of years.

Today I decided I was fairly happy with the setup so far and I decided to
put all information into a new file to play with. I put all 3 groups into
one table. I then looked at the Dummies book I bought and tried to setup a
query that would show all information for the Auxiliary only. I had created
fields for each group along with courtesy copies at the end of each group
before merging them into one. I have the yes/no field set with a check box.
I am thinking that's not the right option as I couldn't get the query to work
properly even with the book in front of me. It still showed all records.

So, what's the consensus? Should all 3 rosters (plus the courtesy copies)
be in one table or 4? Also I created another field for the mailing and set
that up as a yes/no with check boxes. Obviously I can't even think about
doing the mailing from Access yet.

It was also suggested that we put the member ID numbers into a seperate
table and that of course completely confused me. The suggestion was made
because we have a few people who are dual members of the Legion & Sons and a
few who are dual members of the Legion & Auxiliary.

Looking for any and all help & suggestions I can get. I just ask that you
keep in mind that I am not familiar with Access or databases in general.
Quite familiar with Excel though. The reason for the switch is that the post
wants a copy of this file on their computer and they suggested that Access
would be easier once setup.
Lisa

 

[Lisa - NH]

Update: I guess I should have played around a little bit longer. I just
figured out how to get the Auxiliary (& other groups) to show up the way I
wanted. I saw something in the query design view with the word "criteria".
I thought about it for a minute and realized that the yes/no check boxes are
either true/false, on/off etc. So I typed in the word true and ran the query
and there was my information.

I'm not doing any of this playing on my primary file yet. Still looking for
any and all suggestions. If I put member numbers into a seperate table, how
exactly do I do that so that it works properly.

Also I currently don't have a primary key field setup. I don't have the
auto-number field running as I didn't think it was needed. For this type of
membership database, what should the primary key if any? Member ID?
Lisa

 

[Olduke]

The good news is, you are on the right track. You can certainly enter all of
your members into one table unless there is a good reason not to.

It has been my experience in the past that using Yes/No boxes in queries is
tricky. For that reason, I usually just enter the words "Yes" or "No" in the
field.
Fieldname: Mailing Entry choices: Yes or No.

Do you have a member type and does this type refer to the mailings?
Example:
REGULAR members, LIFE members, AUX members all get mailings.
SOCIAL members, TEMP members do not.
You can do your mailing function from this if it's applicable.

Good Luck

 

[Jeanette Cunningham]

Lisa,
one way to save yourself a lot of grief and frustration is to get a sample
membership database and take it apart to see how it works.
There is a sample membership database in the book by John L Viescas called
Building Microsoft Access Applications. It is written for A2003, but would
work in A2007.

Jeanette Cunningham

 

[Lisa - NH]

Hi Jeanette,

Thanks for the recommendation. I'd rather avoid buying another book though.
Anything out there to download that would help?
Lisa

 

[Jeanette Cunningham]

Lisa, sorry, not that I know of.

Jeanette Cunningham

 

[Lisa - NH]

Hi Olduke,

At first I was against the idea of putting them all into one table as I was
too used to the Excel setup and I was afraid that it wouldn't be easy enough
to access just the roster that I wanted. Now that I've played with the query
function and did get it to work with the yes/no check boxes. I'm ok with
having one table for all members. I'm just wondering about the
recommendation to put the member ID #'s into a seperate table. I can sort of
understand why when someone is a dual member, they have 2 different ID
numbers (1 for each group)..

As far as member type. That gets a little confusing. The Auxiliary has
Honorary Life Members (HL), members who have paid one fee for a lifetime
membership (VIM), Juniors (JR), Seniors (SR), those 70 & older with 20 or
more years are paid by the unit, we used to have a lot of 50 year members as
well, but now there's just one. Not sure what the SAL has. The Legion has
the Honorary Life, they have a paid up for life (PUFL), they also
differentiate 50+ year members and 60+ year members....Not sure if I'm
missing anything else as far as that goes. Right now it's just one column
that has the information in it. I was thinking about adding seperate columns
for each different thing to make it easier to see certain listings.

As far as the mailing of the newsletter. We send only one newsletter per
household. There are many members at the same address. There are also
members who do not wish to receive the newsletter and those members who
receive the newsletter via e-mail. It can be a pain every time you add a new
member as you have to check the other members to see if they are at the same
address as someone else. I just re-did the mailing list by exporting the
data out from the Access file into Excel and then sorted by the address field
to see what was what and I found we were sending out some duplicates, etc.
Maybe Access will make this easier to figure out, I don't know.
Lisa

 

[Lisa - NH]

Oh well. I did look for the book on Amazon.com and it is a bit pricey for me
now. Probably wouldn't get much use other than that membership database
either. I'll just keep playing around and seeing how things go. The Dummies
book has definately helpful.
Lisa

 

[John W. Vinson]

Looking for any and all help & suggestions I can get.

I've got a membership database I developed for my church which may well meet
your needs. It lets you enter any number of Members, any number of Groups, and
assign any subset of the members to any group; prints mailing labels,
envelopes, or phone lists; handles families with multiple members at the same
address...

If you're interested in a free copy (no guarantees, limited but nonzero
support, Access2000 version) send me an email at jvinson <at> wysard of info
<dot> com (edit out the blanks and punctuation).

 

[Lisa - NH]

Hi John,

Thanks for the offer. E-mail request sent.
Lisa