Tables - Membership DB - advice needed

  • Thread starter Thread starter Lisa - NH
  • Start date Start date
L

Lisa - NH

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
 
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!
 
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
 
Hi StrayBullet,

Thanks...but I do have a few questions. Since being very new I don't know
what certain things mean. What are: 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
 
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?
Click to expand...

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".
Click to expand...

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?
Click to expand...

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.
Click to expand...

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
 
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
 
<Thanks...but I do have a few questions. Since being very new I don't know
<what certain things mean. What are: 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
 
Lisa said:
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?
Click to expand...

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?
Click to expand...

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?
Click to expand...

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.
 
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
 
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.
Click to expand...

Thanks, I appreciate that info.
Yep! The MemberPK is an autonumber field – simply a unique identifier Of
Click to expand...

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
Click to expand...

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)
Click to expand...

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.
Click to expand...

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.
Click to expand...

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!
Click to expand...

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
Click to expand...

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
 
Hi again StrayBullet,

I did take a little break. Ate some lunch and watched some TV.
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.
Click to expand...

Ok, so to do that the memberID would have to be in the tblMembers?
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)
Click to expand...

Hmmm....I might be having an ahhhh haaaa moment. Does that mean..that
before I input the member#'s (or use a query to build that table), I have to
have specified what group each member belongs to? Already mentioned
something in other post about possibly doing this in the Excel file and
importing it into Access.

Again I thank you and look forward to your reponse on the other post.
Please explain in detail how to do the query to build a table for the
tblGroup & tblGroupType.
Lisa
 
Hi Fred,

I appreicate your help & input. I think it will work well once I get it
going. I'm very particular about things. I always like to improve on
things. In the few years (I think it's 4 now...maybe 5). I've re-done the
Excel version a few times. I definately want to get the Access version set
up well from the begining. I just hope that when I finally decide I've had
enough of doing this.....that the next person does as good of a job with it.
Lisa
 
Hmmm....I might be having an ahhhh haaaa moment. Does that mean..that
before I input the member#'s (or use a query to build that table), I have to
have specified what group each member belongs to?
Click to expand...

Certainly not!!!

That's what data entry is FOR.

You define your members table.
You define your groups table.
You enter the groups into the groups table.
You start entering members into the members table.
*THEN* you define each member's membership in whatever groups you wish, by
adding new records into the membership table, using a Subform as a tool.
 
Lisa,

You've touched on one of my long list of real world issues that I had in
mind. An expert solution may make this your job for life. When you go to
try to hand it off you'll need to find someone who can deal with the more
sophisticated relational abstract structure rather than than the "flat"
structure I suggested.

Sincerely,


Fred
 
Hi John,

Ok, confusion setting in again. I didn't know you could do sub-forms off of
the table. Was that what that little "plus" sign was after relating tables
earlier? (As I mentioned I did scrub what I was doing and will be starting
it again...definately not tonight though.)

I'm definately trying to avoid having to type in every peice of details for
the almost 1000 records as I'd be here forever.
Lisa
 
Hi John,

Ok, confusion setting in again. I didn't know you could do sub-forms off of
the table.
Click to expand...

Well... you can. But DON'T.

Tables re for data storage. you can create your tables first, define their
relationships - all with the tables empty.

Then you would create Forms (with subforms) for data entry and editing.

THEN you would enter your data.

If you already have the data in Excel, you would create the tables; link to
the spreadsheet with File... Get External Data... Link, and run Append queries
to migrate the spreadsheet data into the tables.
Was that what that little "plus" sign was after relating tables
earlier? (As I mentioned I did scrub what I was doing and will be starting
it again...definately not tonight though.)
Click to expand...

I'd suggest opening each table in design view; right clicking in the design
window; and change the Subdatasheet property from "[Auto]" to "[None]".
Subdatasheets do (sort of, crudely) let you enter data into related tables in
a Table datasheet view - but they also wreck performance and are very limited
in utility.
 
Hi John,
Tables re for data storage. you can create your tables first, define their
relationships - all with the tables empty.
Click to expand...

Yes I know.
If you already have the data in Excel, you would create the tables; link to
the spreadsheet with File... Get External Data... Link, and run Append queries
to migrate the spreadsheet data into the tables.
Click to expand...

Is this link permanent? I don't want that as I don't want to keep this
Excel file. I don't understand how the data would link into the proper tables
that way. When I imported from Excel before, everything from one worksheet
went into one table...yes I realize that was just a regular import. This
would make me go back th the thinking that in Excel I would have to create
fields for GroupType, etc.
I'd suggest opening each table in design view; right clicking in the design
window; and change the Subdatasheet property from "[Auto]" to "[None]".
Subdatasheets do (sort of, crudely) let you enter data into related tables in
a Table datasheet view - but they also wreck performance and are very limited
in utility.
Click to expand...

I'll remember that when I re-do the file. As far as creating the tables
first with no data and then the forms and then entering all the data.....I
just don't want to spend that kind of time for this amount of records.
Lisa
 
Hi Fred,
You've touched on one of my long list of real world issues that I had in
mind. An expert solution may make this your job for life. When you go to
try to hand it off you'll need to find someone who can deal with the more
sophisticated relational abstract structure rather than than the "flat"
structure I suggested.
Click to expand...

I didn't fully think about that until tonight. The funny thing is that
right now out of the people who do the membership for the post (me for
Auxiliary and two others). Only one other person has the know how to figure
it out. The other person does his file updating at home in a Works
spreadsheet.

The other thing we're trying to figure out is that a copy of this DB will be
kept at the post. If we give them full access to it to edit the Legion &
Sons membership and do the label printing (which they were thinking about
starting to do at the post...I don't know why as we do it here and there have
been no problems other than my complaining that it was a pain doing it from
Excel with it having two seperate mailing lists based on zipcodes...don't
ask...that was part of why the upgrade to Access to make that easier.) #1 -
they will more than likely make a mess out of that file and #2 - how do I get
the Auxiliary updates into that file and get the other updates out and into
my file. I thought they just wanted this down there for informational
purposes and that we'd do queries to export out each membership roster to
Excel for them to edit...highlight changes and send back. There's too much
darn confusion with this. I don't know why they ever suggested we go this
route. I do however like the concept of the program and what it can do.
Maybe I just give them the file and let them have at it and when I make
Auxiliary updates I write the information down and have my husband bring it
down to the post every few weeks to update the file there. Time will tell.
Lisa
 
Hi John,


Yes I know.


Is this link permanent? I don't want that as I don't want to keep this
Excel file. I don't understand how the data would link into the proper tables
that way. When I imported from Excel before, everything from one worksheet
went into one table...yes I realize that was just a regular import. This
would make me go back th the thinking that in Excel I would have to create
fields for GroupType, etc.
Click to expand...


The link lasts until you delete the link - which you can do at any time
without damage to the data. Linking to the spreadsheet gives you a view very
much (but not exactly!) like importing the worksheet into a single table; it's
just an external table not a local one.

You would need to construct a suite of Append and perhaps Update queries to
migrate the wide-flat data from the spreadsheet into your normalized tables.
I'd suggest opening each table in design view; right clicking in the design
window; and change the Subdatasheet property from "[Auto]" to "[None]".
Subdatasheets do (sort of, crudely) let you enter data into related tables in
a Table datasheet view - but they also wreck performance and are very limited
in utility.
Click to expand...

I'll remember that when I re-do the file. As far as creating the tables
first with no data and then the forms and then entering all the data.....I
just don't want to spend that kind of time for this amount of records.
Click to expand...

Again... YOU DON'T NEED TO TYPE THE DATA. If you have the data in computer
readable form already *that's been done*. All you need to do is move it. Sorry
if I gave the wrong impression above!
 
Hi again Lisa,

My apologies if I made things confusing. I'm between positions right now.
Since this is for the American Legion, if you like I could get the structure
and basic forms set up for you. Also, get the current data imported. Strictly
volunteer basis of course. You can email me at aduphily [at] ptd [dot] net.

Angel
 
Back
Top