Hi
I have an existing table of tenants, with names and dates of birth. I need to add more fields to hold names and dates for partners and dependants : but number of dependants can be from zero to whatever. What is the best way to design a table like this ? I will also need to design a Form to allow me to maintain these variable fields. Thanks in advance.

---

Answer from Richard Rost:

Well, if you could assume that every tennant would only have 3 or 4 dependents, then it would simply be a matter of adding more fields to your tennant table (dep1, dep2, dep3, etc.)

However, some families now days have 9, 10, 12 children.. Just look at the Octomom.

In order to hold an UNLIMITED number of dependents, you would need to create a separate table. Call it a DependantT or whatever you want. Each record would be another dependent, and it would be linked to the tennant table by a TennantID.

TennantT
------------
TennantID (AutoNumber)
FirstName, LastName, DOB, etc

DependentT
---------------
DependentID (Autonumber)
TennantID (Long Int, Foreign Key)
FirstName, LastName, DOB, etc.

Now you just need to set up a FORM with a SUBFORM so you can enter and edit all of this information.

I cover this in my 200-level Access courses, or see this free tutorial:

See this tutorial on how to set up a Form with a SubForm to show related records:

http://599cd.com/tips/access/form-subform?key=BlFAlEx