Table Design

[W. Guy Delaney]

I was taught that every person in a database should have his/her own record.
I know this is not set in concrete in those cases in which you might never
need to sort on FirstName.



In my case, which happens to be a church database, it seems essential for
every person to have his/her own record because many of the reports are
based on either the man or the woman's record, such as Women's Groups, Men's
Groups, women who keep their maiden name, etc.



(Even so, when each person has his/her own record, the names of such couples
must be combined in reports to produce Address Labels and the Membership
Directory. I can resolve this issue, however.)



But with this one person/one record table design, the input data form is at
best awkward and at worst clumsy. With one table named tblAddresses, for
example, and another named tblPeople, you end up with tblAddresses being in
a one to many relationship with tblPeople, since one or more people can
share the same address.



The input form therefore is made up of a main form based on tblAddresses or
a query whose recordset is bases on tblAddresses, and a subform based on
tblPeople or a query whose recordset is based on tblPeople. This design
forces the user to enter an address before being allowed to enter a name.
This, as I say, is very awkward, since the normal way we would expect to
enter data would be to enter the name and then to enter the address.



How do you solve this problem?

 

[John Vinson]

How do you solve this problem?

Just the way you did... with a form and subform.

The only tweak is that I put a (formally redundant) field in the
Addresses table to store the family name, e.g.

Jim & Rachel Jones

To save a little time I've got some VBA code that parses out the first
of the firstnames and the last name and automatically creates a record
in the People table.

 

[W. Guy Delaney]

Please tell me more about how to put the (formally redundant) field in the
Address table.

Also, my I have a copy of the VBA code that parses out the first of the
firstnames and the last name and automatically creates a record in the
People table.

Guy

 

[John Vinson]

Please tell me more about how to put the (formally redundant) field in the
Address table.

Just put in a Text field, FamilyName.

Also, my I have a copy of the VBA code that parses out the first of the
firstnames and the last name and automatically creates a record in the
People table.

Sorry... I'm hoping to find time to get the database put together in
marketable form and sell it, and I'd like to have some copyrighted
"goodies" in it to make it worthwhile.

 

[W. Guy Delaney]

Hey John! Thanks anyway. If you don't sell the database, keep me in mind. I
don't think I can wait until your copyright runs out, but then maybe one of
these days I'll be able to write the code myself. I'm working~

Guy