Church Database

[MissThing]

Trying to put together a simple database for our church directory and
records. We need contact info as well as DOB and group involvement etc. Make
directories, labels, email groups and basic reports. Which I had no problems
with. What I am having issues with is the family relationship thing. We
often need to do mailings per household as well as individuals. What is the
best way to "group" my families. And how should they be entered? I thought
making a check box to designate a "head of household" and when entering
choose a family. Or have a seperate form for "families" THEN enter
individuals designateting them to that family. I don't want to have a whole
lot of tables and things. I really want this as simple as possible with as
little things that could get messed up down the road.

also this will hold our deceased records. Should we have a whole seperate
DB for that? or just have it together. This is not the main issue though and
can handle that at some other time. Right now I just have deceased as a
option under status.

Thank you so much for your time!

Lyndsey

I should mention I'v just altered the sample contacts template.

 

[Steve]

Lyndsey,

I would like to offer to create this database for you. The database would
include your deceased records. I could provide you the database quickly and
you could have it up and running in a short time. There would be a modest
fee for the database. If you are interested, contact me.

Steve
(e-mail address removed)

 

[John... Visio MVP]

Lyndsey,

I would like to offer to create this database for you. The database would
include your deceased records. I could provide you the database quickly
and you could have it up and running in a short time. There would be a
modest fee for the database. If you are interested, contact me.

Steve

stevie you have no shame you will pimp your questionable services to anyone.

Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!
He offers questionable results at unreasonable prices.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

Please do not feed the trolls.

John... Visio MVP

 

[Dennis]

Lyndsey,

Allen Browne has an article that discusses your issues. I don't know if it
will answer your question or not. Here is the link:

http://allenbrowne.com/AppHuman.html

I've toyed with this idea also for one of my membership databases, but have
not done anything yet.

My thoughts, for what they are worth:

tblFamily Table - like Allen's tblGroup table.
keyed by FamilyNo - Automatic number field
Flds: Family Name
Address
City
St
Name
Head of Household Member Id (foreign key to the tblMember
table).

tblMember Table - Like Allen's tblClient table.
Key: MemberNo - Automatic number field
Flds: Family No
Title (Mr, Ms, Mr & Mrs, etc.)
First Name
Middle Name
Last Name (of just one field for name)
Relation (Head of household, father, mother, son, daughter, etc)
Birthday
Date of Death (if this field is not 0, then person is deceased)
(no use to have a yes/no field AND a date of
death field.
Notes


As for the entry screen:

I've asked the same question on this forum as you did about the data entry
screen and I've not received a good answer.

You could do two separate data entry forms / screen, but that is a data
entry paid.

I think a better option would be to create a Family Form that has a member
sub-form on it.

The top havel of the Family form would allow the user to enter the "Family"
house information. This form would have the tblFamily as it RowSource.

The bottom half of the form would be the Member sub-file form where you
would enter the individual family members. This sub-form would retrieve the
family number from the master form. When the head of house hold member is
entered, this sub-form would set a "head of house hold member id" variable in
the family form

This design is not as flexible as Allen's design because it does not allow
one person to be a member of multiple family as Allen's design does.
However, Allen's design is a bit more work to implement.

As for the deceased information, I would include that information on the
member record just for simplicity.

Which sample template did you modify - Microsoft's?

I would love for other people to add their opinions as I would love to have
an answer to these questions.


If you have more detailed questions, please post them and I will try to
answer them. I have a vested interest in this only because I have the same
question.

If you have problems setting up the form and sub-form (which I did when I
first learned about sub-forms), please post to this question. I've had a LOT
of help for this forum whilel I was working with my software for a local
charity, so I'm happy to help you.


Good luck.

Dennis

 

[Fred]

I've databased alot of organizations and wrestled with your issue.

IMHO

The Allen Browne approach referenced by Dennis is very sophisticated,
versatile, powerful & abstract. (that's what you get from someone who is
one of the smartest on the planet at this) Those are 4 things that you might
want to avoid unless you needed them, which you probably don't.

So then the question becomes whether you need a full "two tier" system (with
Families and People both being entities) vs. just "stretching" a one "tier"
system (people). You probably need a two tier system.

And so basically that means do what Dennis said. Except that I think he
misread yo on one point....I think you intended your "head of household"
idea as an alternate to the two tier method.....and so now you don't need it
and he was trying to add it. (or maybe I misread you )

So, recapping, (change all names as desired) make a "Families" table, (PK =
autonumber "FamilyID" field. Make a "People" table with an integer (FK)
"FamilyID" field. Link the 2 "FamilyID" fields.

You main data entry form will be a "Families" form with a datasheet style
"People" subform.

Every person belongs to a family, even if a "Family of One"

So, to enter a person, go to that main form, find or enter their family,
then enter the person in the Person subform.

 

[Dennis]

Fred,

You correct about Allen's approach. I did not mean for Lyndsey to follow
that, I meant it more as background information. However, I guess I should
have included that little bit of informaiton. Whoops. Sorry, my mistake!

The reason I suggested keeping the head of house status field / flag was two
fold.

1. This will enable the churc to easily send a mailing to just "the
family" via the head of house hold. The mailing label report would select
every member with a head of household status.

2. I suggested that the sub-form puting the head of household "people id"
on a data field on the family table. I was thinking that way the Family
Table could link directly to the People table using the head of household's
"people id" as a foreign key.

This would enable Lyndsey to create a name search combo box on the main form
where she can enter the head of household's name, have it display the family
names in the drop down combo box, and let her users select the family from
the list.


Lyndsey

Please do try to implement Allen's full approach. I agree with Fred., the
two tier is bad enough.

Also, I have some code that allows to to specify how many lables to skip on
a page before you start printing the actual labels. That way you don't have
to want labels. I forgot where I got it from, but I will be glad to pass it
on to you.

If you need more help, please post your questions. Most of us still believe
in helping each other for free.

If you have problems developing this form, please let me know. I've been
thinking about it for quite a while and I would not be opposed to developing
a form form my own uses and then giving you a copy.


Dennis

 

[Dennis]

Lyndsey,

I'm sorry, my brain was working faster than my fingers.

While I typed "Please do try to implement Allen's full approach.", I was
thinking "Please do NOT try to implement Allen's full approach."

As Fred stated, the two tier approach should work great for what you are
doing.


Good luck.


Dennis

 

[Kathy R.]

Lyndsey,

I'm also working on a church database - actually re-creating one that I
made more than a decade ago. I've learned a lot since then and
discovered several things I did wrong that needed correcting. I am by
no means an expert, but with the help of these fine folks here (barring
Steve, who seems to keep asking for money even though these are FREE
forums and advertising isn't allowed), I keep getting better.

You will definitely want at least two tables, one for Family information
and one for Individual Information.

tblFamily
FamID (a unique autonumber)
FamLastName (the family's last name - beware, not all people in the
family have the same last name, but if you're addressing Mr. and Mrs.
it's handy to have here)
Address (street address/PO Box)
City
State
ZipCode
HomePhone

tblIndividual
IndID (a unique autonumber)
InFamID (a foreign key to link to the tblFamily)
FirstName
MiddleName
LastName (for those folks that have blended families or hyphenated last
names)
Suffix
Gender
ContactStatus (I use "Primary Contact, Secondary Contact, Child, Other
Adult" - this way you can mail to individuals or families)
DateOfBirth

A couple of other things to consider:

Phone numbers - There's two types of phone numbers - a home phone,
linked to the family/address and other phones linked to individuals. I
finally decided to have a separate table for phone numbers, linked to
the tblIndividual. People have cell phones, work phones, cell phones
for work...

Email addresses - this could go into the tblIndividual, but I am also
finding that I'm getting more and more alternate email addresses. For
that reason I'm also breaking the emails out into their own separate
table linked to the Individual's table.

Regular addresses - I've actually put the addresses in their own
separate table too. We have several people that head south for the
winter. Instead of having to change their address every six months, I
can now just check which address is active. This also covers kids that
head off to college. They have both a home and a college address.

Committees and Groups (everything from Church Council to 1st Grade
Sunday School Class to Volunteer Gardeners) - DON'T do as I did the
first time around and have a yes/no field in the tblIndividual for each
committee/group! It's a nightmare to keep up to date that way and is
just plain poor design. Instead you'll need two more tables so that you
can handle One-to-Many relationships going both ways (One member belongs
to many groups, One group has many members.

tblGroup
GroupID
GroupName
GroupDesc

tblGroupMembers (I call this a join table, but there's probably a proper
name for this kind of table)
GroupMemID (primary key autonumber)
GMGroupID (foreign key linked to tblGroup)
GMIndID (foreign key linked to tblIndividual)
GroupPos (president, chairperson, member, etc.)
Term

Phew! It looks complicated here, but once you have it set up correctly,
entering information is quite easy. If you go with just the Family and
Individual tables, you'd could use a pretty simple Form/Subform. The
main form is where you'd enter the family's information (Last Name,
address, home phone), with a Subform, that is linked via the
Parent/Child property, where you enter information for each individual.

Having said all that, I am just an "upper level beginner." Hopefully,
if I've pointed you in the wrong direction here, the experts will step
in and correct me!

Good Luck, and remember, there's no dumb questions!!

Kathy R.

 

[Dennis]

Kathy,


I like what you did winter and summer address. Very nice. We have the same
issue here in Florida, except in reverse.


Dennis

 

[Dennis]

Kathy,

When you produce a name and address query, which address do you use? Or do
you have to use VBA code to obtain the current addres?

Also, I noticed that you do not have a family relationsihp field (Husband,
wife, son, daughter). Did you find that you did not need it?



Dennis

 

[Kathy R.]

Kathy,

When you produce a name and address query, which address do you use? Or do
you have to use VBA code to obtain the current addres?

A simple query at the moment.

SELECT tblFamily.FamLastName, tblIndividual.FirstName,
tblIndividual.ContactStatus, tblAddress.Street, tblAddress.City,
tblAddress.State, tblAddress.ZipCode, tblAddress.ActiveAddress
FROM (tblFamily INNER JOIN tblIndividual ON tblFamily.FamID =
tblIndividual.InFamID) INNER JOIN tblAddress ON tblFamily.FamID =
tblAddress.AdFamID
WHERE (((tblIndividual.ContactStatus)="Primary Contact") AND
((tblAddress.ActiveAddress)=Yes));

The "ActiveAddress" field is a Yes/No field with its default value set
as yes. Most addresses are active and this way the user doesn't have to
click "Yes" every time they enter an address.

I'm still working out the kinks, and need to get the syntax right so
that I can do Mr. and Mrs. John Doe and Ms. Jane Smith (involving
prefixes and whether there is a secondary contact or not), but I don't
think that'll be a problem.

I can also take this one step further and add the tblGroup and
tblGroupMembers to the query so that I can pull up names/addresses for
just the Trustee Committee or for the newsletter mailing list, etc.

I am curious to hear from the experts though... is a query the thing to
use here, or is there some VBA code that would make the job easier? For
years I was the only person to use the database so I just changed the
query by hand each time. It would not be a pretty sight in the office
though if anything should ever happen to me. I know there's a way to
make a form for the user to choose which group they'd like to see a
report/list/mailing labels for and that is one of the things I'll be
adding to the design this time around. That will require some coding,
but the underlying query will remain the same.

Also, I noticed that you do not have a family relationsihp field (Husband,
wife, son, daughter). Did you find that you did not need it?

I use "Primary Contact," "Secondary Contact," "Child," "Adult Child" in
place of those. Not every adult male is a husband, not every adult
female is a wife. The primary contact would be the head of household -
a single male or female, or the husband of a married couple. The
secondary contact would be the wife. A child could be either a son or a
daughter. I can figure out which because I also have a "gender" field
(another one of the bits of information we Methodists need to track).

Each individual is a member of a family (linked on the FamID), even if
that individual is a family of one.

Thanks for your interest Dennis. The more questions and ideas I hear
the better. It makes me think about things I may not have thought of
and keeps me focused on the proper way to do things!

Kathy R.

 

[Dennis]

Kathy,

Your comment: I am curious to hear from the experts though... is a query
the thing to use here, or is there some VBA code that would make the job
easier? For years I was the only person to use the database so I just
changed the query by hand each time. It would not be a pretty sight in the
office though if anything should ever happen to me.

My Response: I’m not a expert by any means, but from all my questions in
this forum and from what I’ve read, queries or SQL statements in the Form’s
Source property is the way to go.


Your comment: I know there's a way to make a form for the user to choose
which group they'd like to see a report/list/mailing labels for and that is
one of the things I'll be adding to the design this time around. That will
require some coding, but the underlying query will remain the same.

My Response: I’ve already done this, if you want I could post the code
here. There are a couple of tricks. In the On Open event of the report, you
have it open a form by using the command

strDocName = "frmMemberInfo"
DoCmd.OpenForm strDocName, , , , ,acDialog

You want to have the popup window as a dialog box, this makes it a “modalâ€
(sp?) window. When user had entered all of the desired parameters and hit
the Print / Process report button, don’t close the pop-up window – instead
hide the pop-up window by:

Me.Visible = False

The window will no longer be visible, but you still have access to all of
the data. From what I’ve read and from what people on the forum suggest,
don’t return the values from the pop-up via global variables, instead you can
access the variables in the pop-up window from within the calling VBA code by:

mylocalvariable = Forms!formname!controlname

mylocalvar = forms!frmMemberInfo!cboCustNo would return the value of the
control cboCustNo on the frmMemberInfo form.


Fred said he had to address the issue of couple that did not take the other
person’s name – such as Mark Jones and Mary Smith. Do you have that issue,
and if so how are you address it?

Why do you have a Family Last Name on the Family table?


Also, on the data entry form, do you provide a way to search for a family
name (I assuming that is what the Family Last Name is form, but want to make
sure).

Also, can you type in a name and find to which family it is associated?

Also, I’m read through you description again and did not see where you
describe you data entry screen(s). Would you be so kind doing that? I’m
having trouble visualizing the organization of the data entry form?



Thanks

Dennis

 

[Fred]

To: Everyone

Lots of good thoughts & ideas.

I write and run db's for a lot of organizations. And the one thing that I
learned is that the design needs to be driven by the mission for your DB, and
the particulars of the mission and the organization.

There is probably ony one sentence that I'd disagree with, and that is when
Dennis said that listing a seperate family name would be a duplication of
data and thus violation of normalization. The people's names are, of course
an entity. But the family name is also an entity, which is the name that
they wish their family to be called by. Although last names would often be
duplicated, "often" doesn't meet the standard for being able to derive one
from the other.

Lastly, regarding the level of user that the DB is designed for, I do a lot
of databases designs for people who are slightly smart and who have received
about 1 hour of Access training. I teach them what tables, linked tables,
queries, form and reports are, and what they do. Also how to set criteria
in a query design grid. Of this approach is good for some situations and
terrible for others. It does cut development time down to about 1/4. Which
is good for me because I'm really weak on a lot of developer stuff (coding
etc.)

 

[Kathy R.]

Fred said he had to address the issue of couple that did not take the other
person’s name – such as Mark Jones and Mary Smith. Do you have that issue,
and if so how are you address it?

If I'm addressing both it would be Mr. and Mrs. Mark Jones (similar to
if it were Mark Jones III and Mary Smith, I'd address them as Mr. and
Mrs. Mark Jones III even though Mary isn't the III) Singly, It'd be Mr.
Mark Jones and Ms. Mary Smith.

Why do you have a Family Last Name on the Family table?

From Fred: There is probably ony one sentence that I'd disagree with,
and that is when Dennis said that listing a seperate family name would
be a duplication of data and thus violation of normalization. The
people's names are, of course an entity. But the family name is also
an entity, which is the name that they wish their family to be called
by. Although last names would often be duplicated, "often" doesn't meet
the standard for being able to derive one from the other.

Hmm... hadn't really thought of that before. I suppose, since if I'm
using I consider the last name of the primary contact to be the "family
name," that it would be duplicate data. But as Fred says, "a family name
is also an entity." For now I'm going to leave the field as it is.
But I will think on it some more. If anyone else has an opinion, either
pro or con, and would like to share their reasoning, I'd love to hear it.

Also, on the data entry form, do you provide a way to search for a family
name (I assuming that is what the Family Last Name is form, but want to make
sure).

Also, can you type in a name and find to which family it is associated?

Do you mean type in "Bob Jones" to find out if he's Tom and Sue Jones'
son or Bill and Jane Jones' son? I haven't built that capability in.
Our membership isn't so large that we don't know the family
relationships. Or, if we don't know there's at most, only a few
"Joneses" to click on and see. I can see that this would be a problem
if the data entry person didn't know the families, especially if the
child had a different last name. The search combobox on my main family
form is restricted to primary contacts to make the list shorter, but it
could be easily adapted to include everyone and then jump to the
associated family record on that form.

Also, I’m read through you description again and did not see where you
describe you data entry screen(s). Would you be so kind doing that? I’m
having trouble visualizing the organization of the data entry form?

There are two main data entry forms.

The first is to enter information about the family, home address and
basic information about the individuals in that family. This is a
"first contact" type of form. In a church setting we'll get basic
information like name and address long before we get detailed
information like birthdate.

It's a main form with two separate subforms.

The main form contains the FamLastName, FamilyNotes (a memo field), and
a combobox that I use to search for a family and jump to their record.
It displays FamLastName, FirstName (of primary contact). Easy to use
- type in a few letters, tap F4 and choose a name, or for mouse users,
just click the drop-down arrow and choose.

Subform 1 is the address information in form view, with navigation
buttons so you can add or go to a second address. Fields are street,
city, zip, homephone, address type (primary, winter, college, etc),
active address.

Subform 2 is for the basic individual's information. It is in datasheet
format and contains FirstName, NickName, LastName, Suffix, Gender,
ContactStatus (Primary, secondary, child), and Membership Status
(Member, Constituent, Newsletter Only)

--------------

The second main form is for detailed Individual information. I haven't
redesigned this one yet - my original form was created in Access 95 long
before tabbed windows were available. But I envision a main form with
The Individual information on it title, first, middle, last, nickname,
maidenname, birthdate, marriagedate, notes, occupation, work place. All
of those details we gather over time.

Subform 1 for address information (handy when you have two people with
the same name and you're trying to figure out which is which quickly).

Subform 2 for phone number info (personal phone numbers like cell or work)

Subform 3 for email info

Tabs for membership information one each for Joining, Termination,
Baptism (it's a church database), and Death. And a tab for
committees/groups. I haven't designed anything with tabs yet so it
promises to be a learning experience!


Kathy R.

 

[John W. Vinson]

If I'm addressing both it would be Mr. and Mrs. Mark Jones (similar to
if it were Mark Jones III and Mary Smith, I'd address them as Mr. and
Mrs. Mark Jones III even though Mary isn't the III) Singly, It'd be Mr.
Mark Jones and Ms. Mary Smith.

From Fred: There is probably ony one sentence that I'd disagree with,
and that is when Dennis said that listing a seperate family name would
be a duplication of data and thus violation of normalization. The
people's names are, of course an entity. But the family name is also
an entity, which is the name that they wish their family to be called
by. Although last names would often be duplicated, "often" doesn't meet
the standard for being able to derive one from the other.

Hmm... hadn't really thought of that before. I suppose, since if I'm
using I consider the last name of the primary contact to be the "family
name," that it would be duplicate data. But as Fred says, "a family name
is also an entity." For now I'm going to leave the field as it is.
But I will think on it some more. If anyone else has an opinion, either
pro or con, and would like to share their reasoning, I'd love to hear it.

My church database does have a family name field. For one thing, asserting
that all members of a household should have the same surname is not true in
all households! I'm John Vinson; my wife is Karen Strickler. There are two
sisters in my church, living together; both are widows, and both kept their
married names; there are lots of such anomalies. I'd much rather have the
flexibility to address a family as "The Andersons", "David & Angelina
Ramirez", "Mr. & Dr. Roberts", "Ann Jones and Mary Smith", or however *that
family* would prefer to be addressed.

 

[Dennis]

Kathy, Fred, John,



"Why do you have a Family Last Name on the Family table? There is probably
ony one sentence that I'd disagree with, and that is when Dennis said that
listing a seperate family name would be a duplication of data and thus
violation of normalization"


Ah, I see you point and agree. Thanks for the education! I love to learn
and you folks have made me think about things that I've not had to think
about before. I learn a little more each day.



Kathy,

Good luck with your database.


Thanks,

 

[Fred]

Hello Kathy,

Some answers get driven by design principles that are good to always
follow, some get driven by (those with) practical experience, but your
particular needs should also drive answers......in the end what's "right" is
what works well for you.

In general, the more thorough/powerful table structures accomodate a wider
range of scenerios in a fully databased fashion(vs. stuffing exceptions into
notes fields etc.) , but they are usually are also more complicated and more
work. Having separate tables for families and individuals is the most
powerful/versatile of the discussed structures (short of/ not counting Allen
Browne's more powerful and abscract structure only briefly mentioned) But
"powerful/versatile" can be a bad idea for a particular situation if it is
overkill.


The one thing I question structurally in your description is a subform for
addresses. This also implies a separate table. IMHO addresses are "one to
one" information for one of the other tables, and thus should probably be in
the table and form with that other entity.

Good luck!

Fred

 

[Kathy R.]

The one thing I question structurally in your description is a subform for
addresses. This also implies a separate table. IMHO addresses are "one to
one" information for one of the other tables, and thus should probably be in
the table and form with that other entity.

Hi Fred,

Normally I would agree with you about the addresses. I do, indeed, have
a separate table for addresses. Within our congregation we have close
to a dozen families that head south for the winter (2-3 months at a
time) to permanent addresses there. I have to change their addresses
each winter and spring. In addition, we have another half dozen or more
college students. They have both home and college addresses and would
also need to be changed twice a year. Plus, the church is in a college
town and we have students from the college that attend that we keep
track of - again a home and college address.

For this reason I've put the addresses in a separate table. This way I
can just check which is active instead of changing the address back and
forth each year. I debated with myself about this and finally decided
that, once I have the tables/forms/reports setup, it would be easier to
just check a yes/no box than change the address each time.

Thank you for your comments. I really appreciate them!

Kathy R.
PS I won the debate, by the way. ;-)

 

[Fred]

Kathy,

Of course you are absolutely right.

Sincerely,

Fred


PS: What was the debate?

 

[Kathy R.]

Fred
PS: What was the debate?

That would have been the debate with myself (which is the reason I'm so
sure I won) about whether or not to put the addresses in a separate
table. It was just my slightly skewed sense of humor coming to the fore


Kathy R.