Problem re- establishing a relationship

mjburgard
mjburgard used Ask the Experts™
on
Somebody did some editing on the Access Database over the summer and messed up the relationships I had in it.  I had to create a new table (columns ClassnumberN (key), Classnumber, ClassPrefix).  I have a numbered field (ClassNum) in a table called Appointments that I use to reference this table so we can keep track of which class a student is seeing my office for help in.  My problem is now with the relationships.  I created a one to many relationship between the two tables, but if I select a class for one student, it changes the classes for all of the students in the database.  I am fairly sure this is a referential problem, but this was not how it was working when I left for the summer.  
I am having trouble finding the issue.  Any help would be appreciated.  (I am trying to avoid recreating the entire database and importing the data.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
PatHartman
Distinguished Expert 2017

Commented:
Please post a picture of the relationship diagram.  Make sure we can see the relevant parts.
mjburgard

Author

Commented:
relationship.png
mjburgard

Author

Commented:
I was looking at the properties for this field in my form, shouldn't this be bound my the variable I am looking up?
Distinguished Expert 2017
Commented:
ClassNumbersN looks like it is a multi-value field.  I couldn't even begin to tell you if this would work.  Best practice is to get rid of ALL the abomination data types and create standard m-m relationships with visible junction tables that you can control.

Appointments - StudentInfo - can't see the related fields but it looks like this is a pathological relationship.  You have StudentN in the Appointment table and AppointmentN in the student table.

RI is not enforced on any relationship.  Simply drawing the lines does nothing for you.  RI MUST be enforced or don't even bother.
mjburgard

Author

Commented:
While this was not ultimately the solution it did help me get there with a some other good advice that helped solve some things.

The problem has been resolved by finding a recent backup and restoring it then entering the 30 or so entries that needed to be restored by hand.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial