On Tue, 04 May 2010 13:16:44 -0700, Lisa Missenda wrote:

Hi All, I am also creating a church database. I was doing the same thing. Head of House table and dependant table.
It was working fine but I read somewhere that for greater normalization of my
database i might want to take another approach. Namely a complete member
table and another table that has adresses ( which tend to be unique to the
household) But I'm getting caught up in how the dependants can be connected to
this unique address table primary key.

My church membership database (you're welcome to a free copy if you're
interested, just email me at jvinson at wysard of info dot com) has a
Families table related one to many to a Members table:

tblFamily
FamilyID autonumber primary key
SortName last name or other name for sorting
DisplayName e.g. "Mr. & Mrs. Joe Jones", "The Benson Family", "Tim Roberts
& Mary Jones"
address, phone, email, etc.

tblPeople
MemberID autonumber primary key
FamilyID Long Integer fk to Families
MemberNum optional "giving envelope number"
LastName
FirstName
MiddleName
Suffix

There's no "head of family" - everyone in the family is on the same level.

I included a "MoveOut" button on the Members subform which takes a person and
creates a new record in tblFamilies and assigns them to that new FamilyID
(e.g. when a child moves out on her own).

--

John W. Vinson [MVP]