A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Tables - Membership DB - advice needed



 
 
Thread Tools Display Modes
  #1  
Old March 5th, 2008, 09:42 PM posted to microsoft.public.access.tablesdbdesign
Lisa - NH
external usenet poster
 
Posts: 49
Default Tables - Membership DB - advice needed

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
  #2  
Old March 6th, 2008, 02:38 AM posted to microsoft.public.access.tablesdbdesign
StrayBullet via AccessMonster.com
external usenet poster
 
Posts: 48
Default Tables - Membership DB - advice needed

OK, here's my take on it... should get you started:

tblMembers:
MemberPK (PrimaryKey)
strFirstName
strMidInitial
strLastName
strSuffix
strAddress1
strAddress2
strCity
strState
strZip
strPhone
strEmail
dtDOB
blnNewsLetter (Yes/No checkbox if newsletter is to be sent)
strNewsLetterMethod (stores selection from a form dropdown of “Email, Post,
etc”)
memNotes

tblMembership: (one:many relationship to tblMember)
MembershipPK (PrimaryKey)
intMemberFK (ForeignKey to tblMember)
intMembershipType (ForeignKey to tblMembershipType)

tblMembershipType: (one:many relationship to tblMembership)
MembershipTypePK (PrimaryKey)
strMembershipType (the names of the membership types)
strMembershipTypeDesc (the descriptions of the membership types)

tblGroup: (one:many relationship to tblMember)
GroupPK (PrimaryKey)
intMemberFK (foreign Key to tblMember)
intGroupTypeFK (ForeignKey to tblGroupType)
strMemberID (MemberID for each membership)

tblGroupType: (one:many relationship to tblGroup)
GroupTypePK (PrimaryKey)
strGroupType (Names of the group types)
strGroupTypeDesc (descriptions of the group types)

tblPayment: (one:many relationship to tblMember)
PaymentPK (PrimaryKey)
intMemberFK (ForeignKey to tblMember)
dtPaymentDate
curPaymentAmount
memPaymentNotes (allows entry of notes or special instructions)

You will then use forms to edit/add members and their information.

Hope this helps!


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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200803/1

  #3  
Old March 6th, 2008, 02:52 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Tables - Membership DB - advice needed

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

  #4  
Old March 6th, 2008, 02:59 PM posted to microsoft.public.access.tablesdbdesign
Lisa - NH
external usenet poster
 
Posts: 49
Default Tables - Membership DB - advice needed

Hi StrayBullet,

Thanks...but I do have a few questions. Since being very new I don't know
what certain things mean. What a str, dt (date?), bin, mem (memo?), int
and cur (current?). Are these things you type into the field name or is it
specifying a type of field setting?

In the tblMembers, the MemberPK. If you meant that the Member ID # is the
PK, that will NOT work as I had previously mentioned. I will have to use an
auto-number field for the PK. Was going to name it PID or something so that
I won't get confused with the Member ID field.

2nd tblMembers question.....NewsLetterMethod. You say it stores info from a
form dropdown. A form drop down on the table? Or is this something that
happens once I start creating forms? Also would that mean that I can't enter
this info until after I have created the forms and I'd have to go through
each member via the form to input the correct info?

Bear with me here as now I've gotten confused again. tblMembership:
intMemberFK (foreign key to tblMember...is this the primary key field from
the tblMember?) Is the MembershipPK here an a auto-id field?
intMembershipType foreign key to tbleMembershipType I assume is the PK in
that table?

Part of what's confusing me on the membership table is that I thought (not
sure I can explain correctly) that it had to have equal info. If the FK is
the PK from the member table, that's close to 1000. the MembershipType FK to
the MembershipType table would have only 5.

tblMembershipType: MembershipTypePK - auto-id field?

I just answered a question myself by actually reading furthur. In the
tblGroup, I see that's where the actual MemberID number would go. So the PK
in the tblMember is an auto-id field. I assume the GroupPK field is an
auto-id field?

tblGroup: GroupTypePK another auto-id field I assume?

tblPayment: Payment PK again another auto-id? Probably wouldn't put the
payment amount. We just need to know if & when they paid the current year.
Also how can I keep track of more than one year? We like to keep data for at
least the current year & previous year.

Last question for now. It looks like some of this data will only be able to
be entered via the forms so that I'm sure the proper info gets into the
proper place....PLEASE tell me that I can at least import from Excel the
name, address, phone, e-mail & DOB information?

My plan was to figure out what fields would be in the main table and then
edit the information that I exported out to Excel to match that so that I
could import it into a new table and then add the MemberPK field to it. Is
that ok? I don't want to have to type in all the information for almost 1000
records. Or do I really need to setup all the tables first and then enter
all data via the forms.....I'll be here forever.
Lisa

  #5  
Old March 6th, 2008, 05:33 PM posted to microsoft.public.access.tablesdbdesign
Lisa - NH
external usenet poster
 
Posts: 49
Default Tables - Membership DB - advice needed

Hi Fred,

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?


In reading further, yes the answer is many of the above. First off, I am
the membership chairman (along with secretary & treasurer) for our American
Legion Auxiliary. Originally I was keeping an Excel file for this
information and sending updates to the person who was in charge of the post
newsletter at that time. My husband took over as Post Editor and I maintain
the mailing list. But it's more than that. I'm now actually maintaining the
full rosters for the whole post. A main file (that is NOT touched), is kept
on the post computer just in case anything were to ever happen to any of the
membership chairmen. Seperate Excel files are broken out of that file for
them to edit. The SAL is doing great as they highlight anything that they've
changed or added so we can update the main file. The Legion isn't quite as
good about this yet.

So we use it to keep track of all 3 organizations. It is helpful if we keep
at least the current year & previous years payment information. We do a
newsletter (so there are also a list of people not members who get this). We
don't necessarily need to print reports that often but we do need to
occasionally print out a roster by organization. We don't necessarily need
to have a long term history as the "paper" rosters are kept for this.

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".


It used to be just for the purpose of sending out the newsletter (except for
the Auxiliary part). Now it's very important that it keeps full track of the
membership and to print the labels for the newsletter. (Note: I also print
laels out at the begining of each year for each organization for sending out
membership cards.)

3. How big is the organization?


The total between all 3 "groups" and the courtesy people is 1000.

After playing with the program and changing things around. I don't like the
all data in one table concept. Too many check boxes and too easy to screw
something up I think. Also I want to avoid having to re-do queries & forms
every year.

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.


I appreciate the info. I'm leaning more toward the setup the StrayBullet
mentioned as it was actually closer to what I was thinking about breaking
things down to after doing reading/research. It's sorta making a little more
sense to me now. I'm sure many more questions are going to arise as I go
along. Especially once I "hit" forms.
Lisa
  #6  
Old March 6th, 2008, 07:49 PM posted to microsoft.public.access.tablesdbdesign
Lisa - NH
external usenet poster
 
Posts: 49
Default Tables - Membership DB - advice needed

I have another question/possible problem. I went into the Excel file that I
had exported. I moved the stuff that will go into the main Members table
into a seperate worksheet and moved them around to be in the order I wanted.
I renamed the fields as well. Once that was ready, I opened a blank Access
Database. I imported the info I just set up into a new table. It asked to
add an auto-number field and make it the PK and I did so and then renamed it
to MemberPK. That worked fine.

I then created the other tables (Membership, MembershipType, Group and
GroupType....I decided to hold off on the Payment one for the time being).

I input the membership types (honorary, paid life, etc) into the
MembershipType table. I then input the group types into the GroupType table.

I then clicked on relationships and figured out how to relate these tables
to one another. That seemed to work ok.

I then thought that I could copy & paste the list of member #'s over from
Excel into the MemberID field in the Group table. It said it imported them
but nothing showed up. I tried a few different ways and nothing.

1. Do I have to wait to input the member #'s until I have forms setup?

2. When I created the members table....in Excel I edited out the listings
for the dual members. We have 1 dual Legion - Auxiliary member and 4 dual
Legion - SAL members. The are listing once per "group" which means they were
listed twice in the All members sheet. Now this is where some confusion sets
in. I was told that in your main table that you only have one person listed
once. Now that means that the PK in that table is going to have 5 less than
the memberID field in the Group table. Am I over thinking this?

3. This may be overthinking at this point as well (or it could be because
it's 1:30pm and I haven't had anything to eat all day).....How do I deal with
these dual members? Do I need to have two listings for each one in the main
member table?

Just when I figure one thing out, I get confused on something else....
Lisa


  #7  
Old March 6th, 2008, 08:02 PM posted to microsoft.public.access.tablesdbdesign
StrayBullet via AccessMonster.com
external usenet poster
 
Posts: 48
Default Tables - Membership DB - advice needed

Thanks...but I do have a few questions. Since being very new I don't know
what certain things mean. What a str, dt (date?), bin, mem (memo?), int
and cur (current?). Are these things you type into the field name or is it
specifying a type of field setting?

str, dt, bin, mem, int and cur are prefixes. They refer to the field type
(str = string, dt = date, mem = memo, int = integer and cur = currency. Use
of prefixes when creating table fields can be helpful in the future when you
are designing queries, forms, etc.

In the tblMembers, the MemberPK. If you meant that the Member ID # is the
PK, that will NOT work as I had previously mentioned. I will have to use an

auto-number field for the PK. Was going to name it PID or something so that

I won't get confused with the Member ID field.

Yep! The MemberPK is an autonumber field – simply a unique identifier Of
course, you can name it anything you like... I would however suggest
something that specifies its origin is tblMember.

2nd tblMembers question.....NewsLetterMethod. You say it stores info from a

form dropdown. A form drop down on the table? Or is this something that
happens once I start creating forms? Also would that mean that I can't
enter
this info until after I have created the forms and I'd have to go through
each member via the form to input the correct info?

Yes, the intent is that the information would be stored here. While under
daily circumstances, you would enter new data via a form, entering existing
data would depend on your method (import or direct entry – direct entry into
the table could be done, so long as the entries are consistent. The values
entered (ex: Email or Post) would then be the rowsource for a combobox aka
dropdown on your entry form, with the controlsource set for the
strNewsLetterMethod field. This way, anytime you make a selection, it will be
stored in the proper field.

Bear with me here as now I've gotten confused again. tblMembership:
intMemberFK (foreign key to tblMember...is this the primary key field from
the tblMember?) Is the MembershipPK here an a auto-id field?
intMembershipType foreign key to tbleMembershipType I assume is the PK in
that table?

Each of the PrimaryKeys for the tables above is autonumber type. In the case
of intMemberFK, it is storing the MembershipPK. This is the basis for its
relationship with tblMember. The one to many relationship allows multiple
instance of the MemberPK in tblMembership. Additionally, intMembershipTypeFK
would store the MembershipTypePK, in effect creating a many to many
relationship between tblMember and tblMembershipType (meaning that while not
required, each member could theoretically have multiple memberships as
indicated in the original post)

Part of what's confusing me on the membership table is that I thought (not
sure I can explain correctly) that it had to have equal info. If the FK is
the PK from the member table, that's close to 1000. the MembershipType FK
to
the MembershipType table would have only 5.

tblMembership would store multiple instances of MembershipTypePK, creating a
one to many relationship. Multiple people would have the same membership type,
so each of those 5 membership types could appear multiple times in
tblMembership, for each member.

tblMembershipType: MembershipTypePK - auto-id field?

I just answered a question myself by actually reading furthur. In the
tblGroup, I see that's where the actual MemberID number would go. So the PK

in the tblMember is an auto-id field. I assume the GroupPK field is an
auto-id field?

tblGroup: GroupTypePK another auto-id field I assume?

Yes, each of the PK fields are autonumber.

tblPayment: Payment PK again another auto-id? Probably wouldn't put the
payment amount. We just need to know if & when they paid the current year.

Also how can I keep track of more than one year? We like to keep data for
at
least the current year & previous year.

intMemberFK is storage of the MemberPK, again creating a one to many
relationship with tblMember. This allows any number of payment records for
each member.

Last question for now. It looks like some of this data will only be able to

be entered via the forms so that I'm sure the proper info gets into the
proper place....PLEASE tell me that I can at least import from Excel the
name, address, phone, e-mail & DOB information?

Import should be no problem, so long as the information is of the proper type.
Excel and Access play very well together!

My plan was to figure out what fields would be in the main table and then
edit the information that I exported out to Excel to match that so that I
could import it into a new table and then add the MemberPK field to it. Is
that ok? I don't want to have to type in all the information for almost
1000
records. Or do I really need to setup all the tables first and then enter
all data via the forms.....I'll be here forever.

Setting up the tables first is definitely the way to go. One way I’ve done it
in the past is to import the Excel sheet as its own table with an autonumber
PK added. Then use queries to create the related tables, including the
necessary fields and the PK into the FK field, copying the data into the new
tables. After the data is checked and confirmed to have copied over properly,
you can actually delete the copied fields from the first table, removing the
duplicate data. That first table then would be your tblMember. I’ve set up a
layout sample to help you visualize the relationships. It’s in pdf format,
www.a2zpa.com/Relationships-for-MembershipDB.pdf

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200803/1

  #8  
Old March 6th, 2008, 08:11 PM posted to microsoft.public.access.tablesdbdesign
StrayBullet via AccessMonster.com
external usenet poster
 
Posts: 48
Default Tables - Membership DB - advice needed

Lisa - NH wrote:
I have another question/possible problem. I went into the Excel file that I
had exported. I moved the stuff that will go into the main Members table
into a seperate worksheet and moved them around to be in the order I wanted.
I renamed the fields as well. Once that was ready, I opened a blank Access
Database. I imported the info I just set up into a new table. It asked to
add an auto-number field and make it the PK and I did so and then renamed it
to MemberPK. That worked fine.

I then created the other tables (Membership, MembershipType, Group and
GroupType....I decided to hold off on the Payment one for the time being).

I input the membership types (honorary, paid life, etc) into the
MembershipType table. I then input the group types into the GroupType table.

I then clicked on relationships and figured out how to relate these tables
to one another. That seemed to work ok.

I then thought that I could copy & paste the list of member #'s over from
Excel into the MemberID field in the Group table. It said it imported them
but nothing showed up. I tried a few different ways and nothing.

1. Do I have to wait to input the member #'s until I have forms setup?


The MemberID could be entered so long as intMemberFK (the primary key number
from tblMember for each member) is entered as well. I suggest using a query.

2. When I created the members table....in Excel I edited out the listings
for the dual members. We have 1 dual Legion - Auxiliary member and 4 dual
Legion - SAL members. The are listing once per "group" which means they were
listed twice in the All members sheet. Now this is where some confusion sets
in. I was told that in your main table that you only have one person listed
once. Now that means that the PK in that table is going to have 5 less than
the memberID field in the Group table. Am I over thinking this?


tblGroup will store intMemberFK (the primary key number from tblMember for
each member) as many times as is necessary (one member can be in a single
group or in multiple groups)

3. This may be overthinking at this point as well (or it could be because
it's 1:30pm and I haven't had anything to eat all day).....How do I deal with
these dual members? Do I need to have two listings for each one in the main
member table?


Relax, take a breather and come back to the database after you've had a
chance to eat. You'll be fine I'm sure.

Just when I figure one thing out, I get confused on something else....
Lisa


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200803/1

  #9  
Old March 6th, 2008, 09:33 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Tables - Membership DB - advice needed

Hi Lisa,

From what I can see, StrayBullet's solution is a more expert way to do what
what you are trying to to do. Hopefully it will work well for you. In a
lot of years of doing this for volunteer organizations, I've seen that "more
expert" can be a minus or a plus. Hopefully for you it will be a 100%
plus. If any more questions, would be happy to answer.....I probably won't
be monitoring this thread unless you let me know at tureks at ameritech
dot net.

Sincerely,

Fred

"Lisa - NH" wrote:

Hi Fred,

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?


In reading further, yes the answer is many of the above. First off, I am
the membership chairman (along with secretary & treasurer) for our American
Legion Auxiliary. Originally I was keeping an Excel file for this
information and sending updates to the person who was in charge of the post
newsletter at that time. My husband took over as Post Editor and I maintain
the mailing list. But it's more than that. I'm now actually maintaining the
full rosters for the whole post. A main file (that is NOT touched), is kept
on the post computer just in case anything were to ever happen to any of the
membership chairmen. Seperate Excel files are broken out of that file for
them to edit. The SAL is doing great as they highlight anything that they've
changed or added so we can update the main file. The Legion isn't quite as
good about this yet.

So we use it to keep track of all 3 organizations. It is helpful if we keep
at least the current year & previous years payment information. We do a
newsletter (so there are also a list of people not members who get this). We
don't necessarily need to print reports that often but we do need to
occasionally print out a roster by organization. We don't necessarily need
to have a long term history as the "paper" rosters are kept for this.

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".


It used to be just for the purpose of sending out the newsletter (except for
the Auxiliary part). Now it's very important that it keeps full track of the
membership and to print the labels for the newsletter. (Note: I also print
laels out at the begining of each year for each organization for sending out
membership cards.)

3. How big is the organization?


The total between all 3 "groups" and the courtesy people is 1000.

After playing with the program and changing things around. I don't like the
all data in one table concept. Too many check boxes and too easy to screw
something up I think. Also I want to avoid having to re-do queries & forms
every year.

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.


I appreciate the info. I'm leaning more toward the setup the StrayBullet
mentioned as it was actually closer to what I was thinking about breaking
things down to after doing reading/research. It's sorta making a little more
sense to me now. I'm sure many more questions are going to arise as I go
along. Especially once I "hit" forms.
Lisa

  #10  
Old March 6th, 2008, 09:46 PM posted to microsoft.public.access.tablesdbdesign
Lisa - NH
external usenet poster
 
Posts: 49
Default Tables - Membership DB - advice needed

Hi StrayBullet,

str, dt, bin, mem, int and cur are prefixes. They refer to the field type
(str = string, dt = date, mem = memo, int = integer and cur = currency. Use
of prefixes when creating table fields can be helpful in the future when you
are designing queries, forms, etc.


Thanks, I appreciate that info.

Yep! The MemberPK is an autonumber field – simply a unique identifier Of


That's what I thought.

Yes, the intent is that the information would be stored here. While under
daily circumstances, you would enter new data via a form, entering existing


Yes I know that once things are setup, you enter data via the forms.

Each of the PrimaryKeys for the tables above is autonumber type. In the case
"clipped"
relationship between tblMember and tblMembershipType (meaning that while not
required, each member could theoretically have multiple memberships as
indicated in the original post)


I figured that out after looking at things for a while. I see you saw
responded to the other post I put in earlier about my continued confusion
about the dual members. I'll check that as soon as I finish this.

tblMembership would store multiple instances of MembershipTypePK, creating a
one to many relationship. Multiple people would have the same membership type,
so each of those 5 membership types could appear multiple times in
tblMembership, for each member.


Sorry, I'm just not getting it right now. Maybe it's better explained in
the other response I haven't gotten to yet.

intMemberFK is storage of the MemberPK, again creating a one to many
relationship with tblMember. This allows any number of payment records for
each member.


Ok, I'll probably better understand that once I figure the rest of this out
and get to that point.

Import should be no problem, so long as the information is of the proper type.
Excel and Access play very well together!


Yep, I did try it with the tblMembers and it worked.

Setting up the tables first is definitely the way to go. One way I’ve done it
in the past is to import the Excel sheet as its own table with an autonumber
PK added. Then use queries to create the related tables, including the
necessary fields and the PK into the FK field, copying the data into the new
tables. After the data is checked and confirmed to have copied over properly,
you can actually delete the copied fields from the first table, removing the
duplicate data. That first table then would be your tblMember. I’ve set up a
layout sample to help you visualize the relationships. It’s in pdf format,
www.a2zpa.com/Relationships-for-MembershipDB.pdf


Hmmmmm queries to create tables. Don't know if that would be easier or not
as I don't know how to do that type of query. Confusion setting in
again...lol! From the Excel file.....would this mean that I could include
the MemberID field and maybe if so inclined create a field for GroupType and
MemberType and specify what's what there. Then import into Access and use a
query to build the other tables, then remove those fields from the
tblMembers? If so that might be easier and how the heck do I do a query to
make a table? I figured out how to do a regular query to show only those
members I wanted.

Thanks for the PDF. Now onto the next messages.
Thanks for the help and being patient with me. Looking forward to your
response.
Lisa
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:42 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.