CODEWITHSUNDEEP
ms-accessms-access-2016
Lou
Lou

Reputation: 2519

Invalid field definition error when trying to link two tables

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.

Upvotes: 2

Views: 4379

Answers (2)

John
John

Reputation: 1004

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

Upvotes: -1

CJtheLion
CJtheLion

Reputation: 34

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

Upvotes: 2

Related Questions