2

I'm trying to link the "Subject" table to the "StudentSubject" table in the image below, but I get the error "Invalid field definition 'SubjectID' in definition of index or relationship." when I attempt to do so. The end-goal is that I want to create a many-to-many relationship between Student and Subject, such that a student can take many subjects, and a subject can be taken by many students. I'm sure this is a basic error, but I haven't found a general solution to this problem, and I'm rusty on Access.

enter image description here

I drag the "SubjectID" from the Subject table over to the SubjectID from the StudentSubject table, and try to create a relationship, checking both boxes for enforcing referential integrity. It returns the error.

Here are the properties for the Subject table:

enter image description here

And here are the properties for the StudentSubject table:

enter image description here

The properties are the same, so I'm not sure why it doesn't work. It works if I don't enable Referential Integrity, but then it's a one-to-one relationship, and I thought it should be a one-to-many, as there should be multiple students taking the same subject.

Would appreciate any advice on this error, thanks.

Lou
  • 2,200
  • 2
  • 33
  • 66
  • StudentID in the StudentSubject table - is it defined as a Long Integer, the same format as the AutoNumber field you are joining it to? – SeanC Apr 24 '19 at 21:31
  • I'm trying to link it to SubjectID, not StudentID, but yes, they are all Long Integer type. – Lou Apr 24 '19 at 21:43

2 Answers2

2

To make this relationship, your joining table StudentSubject SubjectID should not be an autonumber type, it should be a Number type only. The autonumber should be on the SubjectID in the Subject table

CJtheLion
  • 34
  • 7
-1

Something is wrong here...check your fields/data types As long everything is defined correctly you will have One-Many on all sides enter image description here

John
  • 974
  • 8
  • 16