Relationships

[forest8]

Hi

WHen I try to define a relationship - whether it's a one to one or one to
many -- I can't seem to get it created. The relationship says it's
indetermined (or the equivalent).

I am trying to create what I feel is a very extensive database of students
and any activity they have with any teacher for all their classes.

I am using Acesss 2007.

Thanks
Forest

 

[Jerry Whittle]

If you are trying to create a relationship in the Relationships window with
referential integrity enabled, the Parent or 1 table MUST have a matching
record in each of the Child tables. The joining field in the Parent table
MUST be the primary key field.

If you are trying to enable referiential integrity on existing data, this
can be a problem. First make sure that the joining field in the Parent
table(s) is the primary key. If not and when you try to make it a primary
key, you get a duplicates or null error, you'll need to fix that problem
first. The Find Duplicates Query Wizard can be a great help here.

Next you need to make sure that the FK in the other tables have a matching
value for something in the parent tables. For example you may have a student
with a class that isn't in the Classes table. The Find Unmatched Query Wizard
is just the ticket.

You also need to make sure that you have the correct table structure. In
your case you need at least 3 tables - probably four.

A Student can attend many Courses.
A Teacher can teach many Courses.
A Class is a Course with one teacher and many students.

Therefore you need a Student, Classes, Courses, and Teacher tables.

One Course can have many Classes so there is a 1-M relationship.

A Student can attend many Classes so there is another 1-M.

A Teacher can instruct many Classes so there is yet another 1-M.

The Classes table would contain FKs from the Teacher and Courses tables as a
Teacher teaching a Course would be a Class. It would also contain things like
Room Number, Period, etc.

It's possible for a Student to have Classes with the same Teachers. An
there's the problem: a many to many (M-M) relationships.

Therefore there needs to be another table to bridge or link this data
together. It would contain FKs from the Student and Classes table.

That way you can find out thing like what Students are taking which Courses
and what Teachers that they have.

 

[forest8]

Thank you.

If you are trying to create a relationship in the Relationships window with
referential integrity enabled, the Parent or 1 table MUST have a matching
record in each of the Child tables. The joining field in the Parent table
MUST be the primary key field.

If you are trying to enable referiential integrity on existing data, this
can be a problem. First make sure that the joining field in the Parent
table(s) is the primary key. If not and when you try to make it a primary
key, you get a duplicates or null error, you'll need to fix that problem
first. The Find Duplicates Query Wizard can be a great help here.

Next you need to make sure that the FK in the other tables have a matching
value for something in the parent tables. For example you may have a student
with a class that isn't in the Classes table. The Find Unmatched Query Wizard
is just the ticket.

You also need to make sure that you have the correct table structure. In
your case you need at least 3 tables - probably four.

A Student can attend many Courses.
A Teacher can teach many Courses.
A Class is a Course with one teacher and many students.

Therefore you need a Student, Classes, Courses, and Teacher tables.

One Course can have many Classes so there is a 1-M relationship.

A Student can attend many Classes so there is another 1-M.

A Teacher can instruct many Classes so there is yet another 1-M.

The Classes table would contain FKs from the Teacher and Courses tables as a
Teacher teaching a Course would be a Class. It would also contain things like
Room Number, Period, etc.

It's possible for a Student to have Classes with the same Teachers. An
there's the problem: a many to many (M-M) relationships.

Therefore there needs to be another table to bridge or link this data
together. It would contain FKs from the Student and Classes table.

That way you can find out thing like what Students are taking which Courses
and what Teachers that they have.

 

[John W. Vinson]

Hi

WHen I try to define a relationship - whether it's a one to one or one to
many -- I can't seem to get it created. The relationship says it's
indetermined (or the equivalent).

Just in an addition to Jerry's excellent coverage... the "Indeterminate
Relationship" message appears when there is not a unique Index on either
table's joining field. In order to get a one-to-many relationship, you should
be joining to the Primary Key field of the "one" table - or at least to a
field with a unique Index.

If neither field has a unique Index, Access cannot determine which side of the
relationship is the "one" side.