referential integrity problem

[roger]

I have 2 tables, one courses which has like 35 diffrent
courses that the students can take and then a table which
has all the attendance recorsd for students as theya
ttended/completed these ourses. Ealrier there was a 1-many
relationship and there was referential intergrity set on
i.

The need was that some of the courses were no longer
offered and new ones had to be added. When I tried to
delete the courses it would not let me do that. So what I
did was went to the relationships window....temporraily
diabled the join...deleted the old courses,added the new
courses....and then again made the relationship 1-M but
when I tried to create the refential intergity it said
that will not be possible.

I guess that is fine right...I am figuring that this
happened because there are still some entries in the
attendace table which do not have valide courses any moe
in the cousres table. But going forward a student will be
able to enter/attend only courses that are there on the
list and there is nothing wrong with these right?

 

[John Vinson]

I have 2 tables, one courses which has like 35 diffrent
courses that the students can take and then a table which
has all the attendance recorsd for students as theya
ttended/completed these ourses. Ealrier there was a 1-many
relationship and there was referential intergrity set on
i.

That's as it should be.

The need was that some of the courses were no longer
offered and new ones had to be added. When I tried to
delete the courses it would not let me do that. So what I
did was went to the relationships window....temporraily
diabled the join...deleted the old courses,added the new
courses....and then again made the relationship 1-M but
when I tried to create the refential intergity it said
that will not be possible.

Exactly. You have attendance records for classes which do not exist.
What is the meaning of those attendance records? Do you not want to
keep a history of student attendance, even if the class is no longer
offered?

Rather than delete the classes, I'd suggest adding a Yes/No field
[Active] to the class table; set it to No for a course that isn't
currently being offered. The combo box to select classes for
enrollment would select only active classes (using a query with Yes as
a criterion on [Active], but the old classes would still be there so
you could enforce relational integrity.

I guess that is fine right...I am figuring that this
happened because there are still some entries in the
attendace table which do not have valide courses any moe
in the cousres table. But going forward a student will be
able to enter/attend only courses that are there on the
list and there is nothing wrong with these right?

No, it's not right. Without RI you would be able to enter ANYTHING
WHATSOEVER into the class field in the enrollment table, and Access
won't check to see whether it's a real class or not.

 

[Gary Miller]

Roger,

Another approach for this would have been to create an
Active/Inactive flag in your courses table so that you could
keep them for history but not display the Inactive ones for
selection purposes. If you stay on your present course, you
may want to bend the normalization rules a bit and store the
actual course in the record instead of the ID so that you
will always retain that for historical purposes when this
happens again in the future.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________