Invalid field definition error when trying to link two tables

Question

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.

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:

And here are the properties for the StudentSubject table:

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.

Answer 1

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