How do I set up a many to many relationship with the table analyze

[Elise]

I have flat table imported from and excel file. It has student data, parent
data (actually parent 1 data and parent 2 data), and address data.

Some of the addresses have multiple families. In those cases the parents of
one child isn't the same as another child in that home. Some of the
addresses have step children so that while the one parent is the same for
each child the other may be different for each child. And finally, with
multiple people doing the data entry, in some homes with multiple children a
particular parent may be listed in the parent 1 fields in the record for one
child at the address and in the parent 2 fields in a record for another child
at the address. Because of this I can't simply use the address table to link
the parents with the students.

The relationships as I see them are:
address to parent - one to many
address to student - one to many
student to parent - many to many

I used the analyzer and pulled student fields to a student table from the
main table, parent fields to a parent table from the main table, and left the
address fields in the main table.

this gave me the address to parent and address to student relationships but
not the parent to student relationship.

any ideas?

 

[tfossum]

Elise,

I think in truth your relationships are correct, however in practice I think
you will be challenged by dealing with address to parent as a one to many
relationship. It sounds like you are trying to normalize in the db structure
the truth that several parents, in fact several families, may share the same
dwelling. Theoretically if this is the case a single address record could be
created and all pertinent parents would be assigned to that record.

In practice I suspect it would be better for you to think of the address
parent relationship as one to one. A parent is tied to an address record that
is their own, more easily supporting address changes. If five parents share
the same address and one moves out you don't want that update to affect the
remaining four parents, right?

So I guess this is what I would propose:

StudentInformation
- ID <Primary Key>
- Name

ParentInformation
- ID <Primary Key>
- Name
- AddressID <Foreign Key>

Address
- ID <Primary Key>
- AddressData, etc.

ChildParentLink
- ID <Primary Key>
- ChildID <Foreign Key 1>
- ParentID <Foreign Key 2>

This will support:
- a child can have many parents
- a parent can have many children
- the addresses of all of a child's parents may be different.

I think this will really cover you.

Please let me know if this helps or if you have other questions/concerns.

-Ted

 

[Elise]

Ted,

For some reason it never occured to me NOT to link the student table to the
address table. It makes perfect sense now.

I'm still having trouble making all the relationships.

I used the table analyzer and now have:

StudentInformation
- ID <Primary Key>
- Name
- ParentID <Foreign Key>

ParentInformation
- ID <Primary Key>
- Name
- AddressID <Foreign Key>

Address
- ID <Primary Key>
- AddressData, etc.

I'm having trouble developing the Student/Parent link. I made the table:

StudentParentLink
- ID <Primary Key>
- StudentID <Foreign Key 1>
- ParentID <Foreign Key 2>

When I create the relationship I'm getting the wrong type. I keep getting
the address table on the many side of the relationship when I link it to the
other two tables tables. I think it's because I'm using the key fields on
the parent and student tables and linking them to foreign keys on the family
table. Should I be linking the family ID to a foreign key in the other
tables. If so I end up with the family table having only an id field.

This is beginning to hurt my head.

 

[tfossum]

Elise,

[Note: You mentioned a "Family" table in your last post - is this for real
or a typo? If for real then I am confused where you want that to fit in as
that wasn't in your original post. I am assuming it was a typo.]

Well, I made two typos - one that you caught and one that you didn't.

You caught that I was mistakingly using "Child" where I should have been
using "Student". Sorry about that.

The other mistake is the StudentInformation table should NOT have a ParentID
column. So, all of the tables should look like the following. I have added
the data-types just in case that is causing the problem of non-matching types.

StudentInformation
- ID <Primary Key> [Autonumber]
- Name [Text]

ParentInformation
- ID <Primary Key> [Autonumber]
- Name [Text]
- AddressID <Foreign Key to "ID" in the Address table.> [Number]

Address
- ID <Primary Key> [Autonumber]
- AddressData, etc. [Text, or whatever]

StudentParentLink
- ID <Primary Key> [Autonumber]
- StudentID <Foreign key to "ID" in the StudentInformation table.> [Number]
- ParentID <Foreign key to "ID" in the ParentInformation table.> [Number]

Removing the ParentID column from the StudentInformation table might resolve
your problem. If not, I would suggest using the Realtionship manager to set
up the relationships manually (Tools>Relationships). Display all of the
tables and then drag the column from one table to the related column in
another table, select "Enforce Referential Integrity" from the pop-up, and
repeat for all of your relationships.

Access will probably define the relationship between Address and
ParentInformation as One to Many. It will think that many parents can share
the same address. Someone will probably correct me if I am wrong, but I don't
think you will really be able to correct this, nor is it a big deal. You will
just treat it as though it is One to One.
Another thing you might try, and I am not sure if anyone out there has
experience with this, is that if you are asking Access to setup the
relationships for you automatically it could be puking on the fact that we
are using "ParentID" instead of "ParentInformationID" (same for StudentID vs
StudentInformationID). Changing this might be the little bit of extra advice
Access needs to set everything up right.

If you want I can post a screen shot of the relationship diagram of what I
am suggesting.

-Ted

 

[Elise]

Ted

You're right, In my funk yesterday I referenced the Student Parent link as a
family.

I have been successful however. With your help (thank you very much) and
some from the help menu. Here is what I have:

StudentInformation
- ID <Primary Key> [Autonumber]
- Name [Text]

ParentInformation
- ID <Primary Key> [Autonumber]
- Name [Text]
- AddressID <Foreign Key to "ID" in the Address table.> [Number]

Address
- ID <Primary Key> [Autonumber]
- AddressData, etc. [Text, or whatever]

Student/Parent Link
- ID [Autonumber]
- StudentID <Foreign key to "ID" in the StudentInformation table.> [Number]
- ParentID <Foreign key to "ID" in the ParentInformation table.> [Number]
-StudentID + ParentID <Primary Key>

I selected the StudentID and the ParentID and made them both the primary key
in the Student/Parent Link. I made a query to show the parents and students
at each addres and it worked beaudaciously!

Thanks again for all your help




--
Elise King-Lynch

Elise,

[Note: You mentioned a "Family" table in your last post - is this for real
or a typo? If for real then I am confused where you want that to fit in as
that wasn't in your original post. I am assuming it was a typo.]

Well, I made two typos - one that you caught and one that you didn't.

You caught that I was mistakingly using "Child" where I should have been
using "Student". Sorry about that.

The other mistake is the StudentInformation table should NOT have a ParentID
column. So, all of the tables should look like the following. I have added
the data-types just in case that is causing the problem of non-matching types.

StudentInformation
- ID <Primary Key> [Autonumber]
- Name [Text]

ParentInformation
- ID <Primary Key> [Autonumber]
- Name [Text]
- AddressID <Foreign Key to "ID" in the Address table.> [Number]

Address
- ID <Primary Key> [Autonumber]
- AddressData, etc. [Text, or whatever]

StudentParentLink
- ID <Primary Key> [Autonumber]
- StudentID <Foreign key to "ID" in the StudentInformation table.> [Number]
- ParentID <Foreign key to "ID" in the ParentInformation table.> [Number]

Removing the ParentID column from the StudentInformation table might resolve
your problem. If not, I would suggest using the Realtionship manager to set
up the relationships manually (Tools>Relationships). Display all of the
tables and then drag the column from one table to the related column in
another table, select "Enforce Referential Integrity" from the pop-up, and
repeat for all of your relationships.

Access will probably define the relationship between Address and
ParentInformation as One to Many. It will think that many parents can share
the same address. Someone will probably correct me if I am wrong, but I don't
think you will really be able to correct this, nor is it a big deal. You will
just treat it as though it is One to One.
Another thing you might try, and I am not sure if anyone out there has
experience with this, is that if you are asking Access to setup the
relationships for you automatically it could be puking on the fact that we
are using "ParentID" instead of "ParentInformationID" (same for StudentID vs
StudentInformationID). Changing this might be the little bit of extra advice
Access needs to set everything up right.

If you want I can post a screen shot of the relationship diagram of what I
am suggesting.

-Ted

Ted,

For some reason it never occured to me NOT to link the student table to the
address table. It makes perfect sense now.

I'm still having trouble making all the relationships.

I used the table analyzer and now have:



I'm having trouble developing the Student/Parent link. I made the table:


When I create the relationship I'm getting the wrong type. I keep getting
the address table on the many side of the relationship when I link it to the
other two tables tables. I think it's because I'm using the key fields on
the parent and student tables and linking them to foreign keys on the family
table. Should I be linking the family ID to a foreign key in the other
tables. If so I end up with the family table having only an id field.

This is beginning to hurt my head.