Hello all. I also have a similar design problem. We are a
theater/performing arts center.
I am trying to track clients that attend our events, classes and also donate.
We would like to be able to identify households which may inlude one or two
heads as well as children, and be able to customize class brochure mailings,
theater show mailings etc. The problem is that we do not want to mail
flyers/brochures to children for things that are not appropriate for children.
Also, we don't want to mail requests for donations to children. In addition,
we need to be able to track donation amounts and services that our clients
use.
The other issue is how to track the ages of the children over time -
calculating age.
I don't understand how linking a table to itself works and if it will fit my
needs.
Pat Hartman(MVP) wrote:
The way this family relationship is handled in a normalized schema is with a
self referencing table. An autonumber is used as the primary key. A field
defined as long integer is used to hold the FamilyID. When one record is
related to another, the primary key value of the parent record is placed in
the FamilyID field of the child record. The default for the FamilyID field
should be null rather than 0 so just delete the 0 from the default field and
do not replace it with anything.
To build this relationship in the relationship window, add the table to the
window two times. The second instance of the table will have its name
suffixed with "_1" to differentiate it from the first instance. You would
then draw a join line from the primary key of the first instance to the
FamilyID field of the second instance. Select enforce referential
integrity.
On your forms, use a combobox to select the FamilyID field. As the
RowSource use a query similar to:
Select PersonID, LastName, FirstName
From YourTable
Where FamilyID Is Null
Order By LastName, FirstName;
This selects only people who have no FamilyID and so are presumed to be the
head of a household.
Hello all,
[quoted text clipped - 39 lines]
Thanks in advance for your help. Any suggestings or comments are greatly
appreciated.
--
Message posted via
http://www.accessmonster.com