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