Table Design

[Serendipity]

Using Access 2003. I am designing a database for a school. There has to be a
better way for the many names associated with one student. Dad, Mom, StepDad,
StepMom, All the granddads, gradmothers, stepgranddads, stepgrandmothers, and
the great grandparents, gardian, etc. There would be a separate field for
First Name, Middle Name, and Last Name for each of these names. That would be
a whole lot of fields just for the names. And then the same thing would
happen again when trying to make a fields for each of them to have a home
phone, cell phone, work phone, fax, pager, etc., etc. Should this information
be put in more than one field--tied together with a family ID field? Is there
something I could read on this? Everything I can find is too basic.

Thanks,
Marie

 

[KARL DEWEY]

Have one table for people - period. Use an autonumber as primary key. Have
two other fields besides all the other home phone, cell phone, work phone,
fax, pager, etc., stuff to show relations.
The first field shows which student they are related to and the second what
the relationship is - Dad, Mom, StepDad, StepMom, All the granddads,
gradmothers, stepgranddads, stepgrandmothers.
In the Relationship window insert the same student table twice - the second
will have a suffix of _1 hung on it. Drag the primary key filed from the
first to the student relation field. Set referential integrity on.

First you will add the student then their relatives.

 

[Serendipity]

You said, "The first field shows which student they are related to." My
question: Where does the value for this field come from? Seems like it would
be a hassle to look up the autonumber each time.

Also, is each family member a separate record?

Thanks.

 

[Serendipity]

You said, "The first field shows which student they are related to." My
question: How do I get the value for this field. It would seem like a real
hassle to look up the autonumber for the family each time."

Also, is each family member entered into a separate record?

Thanks,

 

[KARL DEWEY]

Use a form and a subform for student and relatives. There will be no need to
lookup the autonumber as the one-to-many relation will auto fill it in for
you if you have the Master/Child links for the form/subform set on the
autonumber primary key field to the relative field.

Yes, each family member will be entered into a separate record.

 

[Serendipity]

I am really, really confused because this is all new to me.

I have a people table (tblPeopleInfo) where I have a field for last name,
middle name, first name, home phone, cell phone, etc., for all phone
possibilities. And then I did the two other fields that Karl told me about: I
called the first one "Family Tie" (this should have info telling what master
family they are in, I think) and the second one "RelationshipType" (the one
that is father, grandfather, etc.) I made the Relationship Type a lookup so
that I could put my values in a table for consistency. I also have another
lookup field: OtherAddressType. And in the relationship window I have the two
copies of the tblPeopleInfo, I drug the fldPeopleInfoId field to the
"fldRelationshipType" field (of the copy of the table). Did I misunderstand?
Should I have drug it to the "Family Tie" field.

How does the form and subform field work? What fields are in the form and
which ones are in the subform. I tried several things but couldn't make it
work.

Thanks!
Marie