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.

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 Removed)


"MissThing" <(E-Mail Removed)> wrote in message
news:596EE388-021E-4022-9A28-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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

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

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.

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

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

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.

MissThing wrote:
> 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.
>

Kathy,


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


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