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.

"AccessHelp" wrote in message
...
Hello all,

I am trying to design a database to store the information of our clients.
I
would like to get some inputs from you guys on the design. Also whether
anyone knows of a web site that I can go to download a sample client
database.

I am facing some difficulties on the design. I will discuss my
difficulties
below.

Before I share with you my difficulties, the way we have our client number
is different from most firm. Our client number is not automatically
generated by Access. Instead we come up with a new client # whenever we
add
a new client. The structure of our client # is, for example, "1234.01".
The
first 4-digit represents the family and the last 2-digit represents the
clients in the family. For example, if we do a job for both father and
son,
the client # for the father is 1234.01 and the son is 1234.02.

In the past, the database was wide-open, and people would go into the
table
to add a new client. For example, if it is a new family, people would
look
up the whole table and come up with a new client # by picking the unused
client #. That whole process created big problems because there were no
controls on the database.

Now I am trying to re-design the database where people use the forms to
add
new clients or modify existing clients.

The difficulties that I am having is how do I design it so that:

1. the user can easily come up with a new client # for a new client
2. the user does not end up creating the same client with a different
client #

Thanks in advance for your help. Any suggestings or comments are greatly
appreciated.