Multiple Small Tables or one large one???

[joshuabarnette]

Here is my problem...

I have a database for maintaining information on members of a church.
The information is currently split over 3 or more tables. 1 Table has
maybe 15 fields, the others have 10 or less. When I go to create a
tabbed form of the multiple tables I get the error that the record
doesn't exist in the main table.

Should I stick with the separate tables and try to get help fixing the
form? Or should I condense to one larger table? Which method is better
and why?

 

[Lynn Trapp]

Without knowing your tables, what data they store, and how they are related
to each other, it is not possible for anyone to help you out. Remember we
can't see your database. Please post the structure of your tables.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[joshuabarnette]

The Table structure is:

family_tbl
-----------------------------------
fnum - Family Number - KEY (No Duplicates)
paddlin1 - Physical Address Line 1
paddlin2 - Physical Address Line 2
pcity - Physical City
pstate - Physical State
pzip - Physical Zip
phone - Phone
fax - Fax
smail - Mailing Address? Yes/No
maddlin1 - Mailing Address Line 1
maddlin2 - Mailing Address Line 2
mcity - Mailing City
mstate - Mailing State
mzip - Mailing Zip

members_tbl
-----------------------------------
fnum - Family Number - Key (Duplicates OK)
mnum - Member Number - Key (No Duplicates)
title - Courtesy Title
fname - First Name
lname - Last Name
suffix - Name Suffix
sex - Male/Female
bday - Birthday
mstatus - Marital Status
aday - Anniversary
mphone - Mobile Phone
wphone - Work Phone
wext - Work Extension
email - E-Mail Address

church_tbl
-----------------------------------
mnum - Member Number - KEY (No Duplicates)
ch_status - Church Status
ch_stat_date - Church Status Date
ch_visit_src - Where is the visitor from?
ch_visit_class - What Sunday Class Should Visit
ch_visit_visit - Would like a visit from the pastor? Yes/No
ch_visit_visit_day - Preferred day of visit
ch_visit_visit_time - Preferred time of visit
ch_visit_mem - Interested in membership? Yes/No
ch_visit_info - Would like more info on faith/church? Yes/No
ch_visit_och_mem - Member of another church? Yes/No
ch_visit_och - Other church Name
ch_mem_date - Church membership date
ch_mem_method - Way joined church
ch_mem_oth_ch - Previous Church
ch_rem_date - Church removal date
ch_rem_method - Why removed from church
ch_rem_oth_ch - Church No Attending

ss_tbl
-----------------------------------
mnum - Member Number - KEY (No Duplicates)
ss_class - Sunday School Class
ss_status - Sunday School Status
ss_stat_date - Sunday School Status Date


mem_note_tbl
-----------------------------------
mnum - Member Number - KEY (Duplicates OK)
mem_note_date - Note Date
mem_note_type - Note Type
mem_note - Note

office_tbl
-----------------------------------
mnum - Member Number - KEY (Duplicates OK)
ch_off - Church Office - KEY (Duplicates OK)
ch_off_date - Date Office Taken

mm_tbl
-----------------------------------
mnum - Member Number - KEY (Duplicates OK)
mm_position - Music Ministry Position - KEY (Duplicates OK)
mm_date - Music Ministry Posistion date


Relationships are:

family_tbl <1 - many> members_tbl
members_tbl <1 - 1> church_tbl
members_tbl <1 - 1> ss_tbl
members_tbl <1 - many> mem_note_tbl
members_tbl <1 - many> office_tbl
members_tbl <1 - many> mm_tbl

I also have further clarification on the issue. When I have a form with
the family_tbl, members_tbl, church_tbl, and ss_tbl it will not
propogate all the tables unless all the information is entered when the
initial record is created. If you go back to add the ss_tbl info or
church_tbl info, you get the message that the "You cannot add or change
a record because a related record is required in table 'members_tbl'."

My question is knowing the data on the <1 - 1> tables would it be
better to leave them separate and fix the form?

OR

Make them into one large table and thus fix the problem?

Which ever you specify, please explain why.

Thanks,

Joshua Barnette

 

[BruceM]

I will jump in for a moment since there has been no recent activity in this
thread. I don't necessarily have a complete answer, but I do have a few
thoughts.
For one thing, there is a sort of happy medium between providing
insufficient information and providing more than is useful. It is not
necessary to post every field when posting your table structure. For
instance, in a personal information table something like Name, Address,
Phone, etc. is sufficient.
I do not have much use for one-to-one relationships, but I think they may be
appropriate in your database with things like Music Ministry. If you need
several fields to describe the parameters of somebody's music ministry (e.g.
sing, read music, play an instrument, etc.), but only a small proportion of
the total are engaged in music ministry, then a separate table may be the
best so that you don't have a bunch of empty fields in most of the records.
Help has some information on one-to-one relationships.
I'm having some trouble sorting out what is going on. For instance,
family_tbl doesn't seem to have anything such as a name to identify the
family. The primary key will distinguish one family from another, although
not in a user-friendly way. Further, is there ever a condition where family
members do not reside under the same roof? It seems rather a curious thing
that phone numbers are associated with individual family members, but
addresses are associated with families.
I see some other things that puzzle me a bit. For instance, what do you
mean to accomplish with the ss table, the mm table, and the office table?
Is the ss table a record of who has attended which Sunday School session and
when they attended?
It may be best to start with households. You could create a household, then
add family members to it. There would be a one-to-many relationship between
households and members. Each member may be associated with several groups
or ministries or whatever. To put it another way, each member may be part
of several groups, and each group may contain several members. This is a
many-to-many relationship, requiring a junction table between two other
tables. For instance, both MusicMinistry and Members would be related
one-to-many with MusicMinistryRoster.
I'm not going too far down that line without knowing if it's anywhere near
correct. If it is, I would suggest setting up a simple test database with a
few families, each containing a few members, some of whom are involved with
music ministry (or whatever). This will let you develop a basic structure
that can be augmented as needed. If the MusicMinistry parts works OK, it's
the same procedure for another group.
Use a pencil and paper to work this out. There's a lot going on here. A
few sentences to describe your intention in non-database terms may be of
help when posting back.