Prev: Conversion To Acc2007 or beyond Suggestions ?
Next: Make Query or Linked Table Read Only
From: Dennis on 25 Feb 2010 23:17
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

From: Dennis on 26 Feb 2010 09:49
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

From: Kathy R. on 27 Feb 2010 16:07
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.
>
From: Dennis on 27 Feb 2010 18:10
Kathy,


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


Dennis
From: Dennis on 27 Feb 2010 18:26

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
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Conversion To Acc2007 or beyond Suggestions ?
Next: Make Query or Linked Table Read Only