Access 2000 - Create a Dependants Table

For a Health Club membership system, I have been tasked with designing a
Parent and Dependants (Family Members) table so we can track what Members are
signing up for classes. My first thought is to have both the Parent and each
of the Family Members in a single table each being assigned their own
CustomerID.

Is this the best way to accomplish this?

My sample layout for my tblCustomers is:
--------------------------------------
CustomerID (Primary Key)
Salutation
FirstName
LastName
DateOfBirth
ParentCustomerID
--------------------------------------

Also, when I coming up with a design for the Member Entry Form, where the
Parent information would be on the Main Form and I would have a SubForm to
enter each of the Dependants (Family Members), I would link my SubForm to
MainForm based on the followign fields from my tblCustomers:
Link Child Fields = ParentCustomerID
Link Master Fields = CustomerID


Any thoughts or sample applications I can look at?

Placing all the people in the one table is a really good approach.

Using a ParentCustomerID foreign key field will limit you to just one parent
per customer. Is that adequate for your needs? Could there ever be cases
where you may need to record that a person has 2 parents?

Could there be more than 2 parents? Step parents, or guardians (for foster
children), or ...?

If you need only 2, a FatherID and MotherID would be useful, like this
example:
http://allenbrowne.com/ser-06.html

If you may need more than two, perhaps this example could help:
http://allenbrowne.com/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tony_VBACoder" wrote in message
...
For a Health Club membership system, I have been tasked with designing a
Parent and Dependants (Family Members) table so we can track what Members
are
signing up for classes. My first thought is to have both the Parent and
each
of the Family Members in a single table each being assigned their own
CustomerID.

Is this the best way to accomplish this?

My sample layout for my tblCustomers is:
--------------------------------------
CustomerID (Primary Key)
Salutation
FirstName
LastName
DateOfBirth
ParentCustomerID
--------------------------------------

Also, when I coming up with a design for the Member Entry Form, where the
Parent information would be on the Main Form and I would have a SubForm to
enter each of the Dependants (Family Members), I would link my SubForm to
MainForm based on the followign fields from my tblCustomers:
Link Child Fields = ParentCustomerID
Link Master Fields = CustomerID


Any thoughts or sample applications I can look at?

Allen, thank you for the links and the information. To answer some of your
questions:

1) In my case, a Dependant (Family Member) would only have 1 parent.
2) No, there would not be a case for 2 parents


"Allen Browne" wrote:

Placing all the people in the one table is a really good approach.

Using a ParentCustomerID foreign key field will limit you to just one parent
per customer. Is that adequate for your needs? Could there ever be cases
where you may need to record that a person has 2 parents?

Could there be more than 2 parents? Step parents, or guardians (for foster
children), or ...?

If you need only 2, a FatherID and MotherID would be useful, like this
example:
http://allenbrowne.com/ser-06.html

If you may need more than two, perhaps this example could help:
http://allenbrowne.com/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tony_VBACoder" wrote in message
...
For a Health Club membership system, I have been tasked with designing a
Parent and Dependants (Family Members) table so we can track what Members
are
signing up for classes. My first thought is to have both the Parent and
each
of the Family Members in a single table each being assigned their own
CustomerID.

Is this the best way to accomplish this?

My sample layout for my tblCustomers is:
--------------------------------------
CustomerID (Primary Key)
Salutation
FirstName
LastName
DateOfBirth
ParentCustomerID
--------------------------------------

Also, when I coming up with a design for the Member Entry Form, where the
Parent information would be on the Main Form and I would have a SubForm to
enter each of the Dependants (Family Members), I would link my SubForm to
MainForm based on the followign fields from my tblCustomers:
Link Child Fields = ParentCustomerID
Link Master Fields = CustomerID


Any thoughts or sample applications I can look at?