Access design problem. How to set up a multiple church directory .

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to set up a state-wide church directory that will contain for each
individual church the: name, address, worship times, telephone, etc PLUS list
the individual church officers their home address, telephone, emails , etc. I
can send a sample of typical information in WORD to illustrate the
information needed above.
THANKS, Don Bone, minister
 
here's a basic table/relationship design that should help you get started:

tblChurches
cID (primary key, autonumber)
cName
cAddressOne
cAddressTwo
cCity
cState
cZip

the above assumes each church has one address. but if a church may have one
or more satellite locations (such as a community outreach center, etc), then
you should have a separate table for "sites", that includes a field
indicating the name and/or type of location and all the address
information - instead of putting address information in tblChurches.

tblChurchComms (that's short for communications)
ccID (primary key, autonumber)
cc_cIDfk (foreign key from tblChurches, long integer)
ccLoc (location of phone, such as office, rectory, etc.)
ccNumber (phone number)

i'm assuming a church may have more than one phone number. if that is
*never* the case, you can ignore this table altogether and just put a phone
number field in tblChurches. if a single church may have multiple "sites",
as discussed above, then you'll need to consider if each site may have one
or more phone numbers.

tblWorships
wID (primary key, autonumber)
w_cIDfk (foreign key from tblChurches, long integer)
wDay (day of week)
wServiceType (such as Sunday School, Early Mass, Benediction, Bible Study)
wStart (time the service starts)
wEnd (time the service ends)

here again, if a single church has more than one "site" that holds worship
services, this table should be linked to the "sites" table rather than to
tblChurches.

tblChurchOfficers
coID (primary key, autonumber)
co_cIDfk (foreign key from tblChurches)
coFirst (first name)
coLast (last name)
coAddressOne
coAddressTwo
coCity
coState
coZip
coTitle

tblOfficerComms
ocID (primary key, autonumber)
oc_coIDfk (foreign key from tblChurchOfficers, long integer)
ocType (such as home phone, work phone, cell phone, email)
ocNumber (phone number - text field, not numeric)

as you can see from the remarks scattered between the "demo" tables above,
you have to carefully analyze the data you want to include in the database
*before* you start to design the tables/relationships. most databases *seem*
simpler than they turn out to be, once you begin to really consider every
aspect of the data.

you didn't indicate what experience you have in data modeling, or in
building Access databases, Don. if you begin to feel out of your depth, i'll
be happy to give you a hand with it (and if you're an experienced user, then
no offense intended!)

hth
 
this site has lots of data models that may be useful
http://www.databaseanswers.org/data_models/index.htm


On Tue, 21 Dec 2004 17:59:05 -0800, Don Bone, Minister <Don Bone,
I need to set up a state-wide church directory that will contain for each
individual church the: name, address, worship times, telephone, etc PLUS list
the individual church officers their home address, telephone, emails , etc. I
can send a sample of typical information in WORD to illustrate the
information needed above.
THANKS, Don Bone, minister
Click to expand...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
On Tue, 21 Dec 2004 17:59:05 -0800, Don Bone, Minister <Don Bone,
I need to set up a state-wide church directory that will contain for each
individual church the: name, address, worship times, telephone, etc PLUS list
the individual church officers their home address, telephone, emails , etc. I
can send a sample of typical information in WORD to illustrate the
information needed above.
THANKS, Don Bone, minister
Click to expand...

Well, as usual - identify the Entities, their Attributes, and their
Relationships. I'd see the following tables:

Churches
ChurchID, Name, Address1, Address2, Street, City, Zip, State

Phones
ChurchID, Phone, Phonetype <you might have several records per
church>

Worship
ChurchID
ServiceName
WeekDay <1 = Sunday through 7 = Saturday>
ServiceTime

Officers
PersonID
LastName
FirstName

Addresses
PersonID
<usual address fields as above>

EMails
PersonID
Email <hyperlink so you can just click it to mail>


John W. Vinson[MVP]
 
John,
I just glanced through this and, without knowing exactly what you were
thinking, thought you might want to add one field to one of the tables:

Officers
PersonID
ChurchID (FK to Churches table)
LastName
FirstName

It seemed that the OP wanted to track the officers of individual churches.
You may have been thinking in a different veign and, if so, ignore
this...<g>

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
 
Tina,
A suggestion about field naming here, and please take this only as a
suggestion. As I read through your table structure below, my head started to
swim with all the fieldname prefixes. I realize this is your own convention
and probably works well for you. However, if you hand a structure like this
off to someone else, it might become very confusing, very quickly. For
example, I would do tblChurchOfficers one of these two ways instead.

tblChurchOfficers
ChurchOfficersID (primary key, autonumber)
ChurchesIDfk (foreign key from tblChurches)
ChurchOfficersFirst (first name)
ChurchOfficersLast (last name)
ChurchOfficersAddressOne
ChurchOfficersAddressTwo
ChurchOfficersCity
ChurchOfficersState
ChurchOfficersZip
ChurchOfficersTitle

tblChurchOfficers
ChurchOfficersID (primary key, autonumber)
ChurchesIDfk (foreign key from tblChurches)
FName (first name)
LName (last name)
AddressOne
AddressTwo
City
State
Zip
Title

Actually, the second on is the convention that I use. I make the assumption
that I don't really need the prefixes to most fields because I can fully
qualify the field name with the table name (or an alias) any time I need to
avoid confusion.

Again, I know this is all cosmetic and whatever works best for you is what
you should go with. I just thought my suggestion might be helpful in case
you ever end up passing your code to someone else.
 
Or maybe John was thinking there is a many-to-many relationship between
Churches and Officers? Can one be an officer in more than one church?

Immanuel Sibero
 
Or maybe John was thinking there is a many-to-many relationship between
Churches and Officers? Can one be an officer in more than one church?

Immanuel Sibero
Click to expand...

Lynn, Immanuel - you're both right; I left that VERY IMPORTANT link
out in my haste!

It's up to the OP to decide whether Lynn's 1:n or Immanuel's m:n
relationship is more appropriate (although a friend of mine is a
Certified Lay Pastor serving three churches, so I'd lean toward the
latter).

John W. Vinson[MVP]
 
Thanks,

Can't find a more fitting thread, so:

Merry Christmas to everyone, the MVP's and other experts.
May the coming year be a healthy and prosperous one for you and your family.


Immanuel Sibero
 
Back
Top