OK. I have a follow up question - I should have asked the first time. In
designing my data base, I understand what you said about creating a child
table to handle multiple children. Now my question is that if I also had to
record multiple entries for language spoken (by parents) do I set up another
table for language?? Assuming that is the answer, then when I create the
subform, do I do a subform for children ages and another subform for
languages or just combine them into a single subform to accommodate multiple
entries for either children ages or multiple languages???? Thanks again.
Let's go back to basics here.
The *FIRST* step in creating an Access database is to put your
computer in Hibernate mode, or turn it off. Go into another room.
Scrounge around and find a pencil and a pad of paper.
Identify the "Entities" - real-life persons, things, or events - of
importance to your application. A child is an entity; a family is a
different kind of entity; a parent is an entity that I suspect you may
also need; a language is an entity.
Each type of Entity gets its own table. Each Entity has "Attributes" -
atomic, non-repeating bits of information that you need to know to
properly represent that entity. For example a child will have a
FirstName, a LastName, DateOfBirth, etc. These attributes will become
fields in your table. Each table also needs a "Primary Key" - a field
or a combination of fields which uniquely identify one entity. Access
may give the impression that a Primary Key should be an Autonumber;
this isn't necessarily true though! The PK should be *unique* (only
one in the table, so a person's name will NOT be suitable), *stable*
(it shouldn't change often, or at all) and preferably *short*. If
there is no suitable field in the table, you can use an Autonumber.
Then you need to see how your Entities are related to one another.
There are three main types of relationships: One to One is actually
rather uncommon, since you can simply combine the information into a
single table. One to Many is almost universal - one Family to several
Children, for example. Many to Many is just as common: some people
speak several languages, any language may have many speakers. A Many
to Many relationship always requires *a new table* to represent the
relationship. For example, you would have a table of Languages; this
might be just a simple one-field table with records for Thai,
Vietnamese, Khemsa, Latvian, German, English, etc. The name of the
language is a perfectly satisfactory primary key: it's unique, stable,
and short. To record what languages a person speaks, you'll need
another table: LanguagesSpoken for example. This might have just two
fields: the unique PersonID and the Language; or you might want to add
other fields for fluency, how the language was learned, etc.
While you're translating these Entities and Attributes into tables and
fields, you might find yourself entering fields with names like
Parent1, Parent2, Parent3 (hey, it's the 21st century, blended
families are very common). If you do, stop; you've found another one
to many relationship, and need another table!
Only when you have the Tables all built should you start thinking
about Forms. To answer your direct question - yes, you'ld have a Form
with two (or more) subforms, one for children, one for languages.
A couple of things to think about: I don't know the purpose of this
database, but I've done some family databases and issues do arise!
What *about* blended families, say where the father, mother,
grandmother and aunt are the adults in the household, sharing custody
of the children? What about split families, where a child spends time
in two separate families? When it comes to language, obviously a
Family doesn't speak a language: people do! Shouldn't the language
table be related (many to many) to the table of people? For that
matter, children and adults are both people; should you perhaps just
have a People table, and indicate in a field whether this person is an
adult or a child?
Sounds like an interesting project; hope this is helpful!
John W. Vinson[MVP]
