Creating a relationship with 2 primary keys

[cunning]

I'm supposed to create a relationship between these two tables with the relationship being StudentID (Transfer student as primary & Transfer School as related)


with enforce referential integrity. I keep getting this error code. I'm not sure what I am doing wrong or what error did I make. Any advice would help. Thank you
https://youtu.be/qrrUcxv1awE <-------- video of the steps i took. (at the end when creating a query I'm supposed to end up with 144+ records)



(To the left is the 2nd page, the right side is the start of the excercise) Sorry

[pbaldy]

The error is because there are one or more records that violate the rule you're trying to apply. Try the unmatched query wizard to find them.

[ssanfu]

The problem is that the TransferStudent table has the field "StudentID" as the PK field AND "TransferSchool.StudentID" is also a PK field.

"TransferSchool.StudentID" should be a FK (foreign key) field.
Add an autonumber field to the "TransferSchool" table and set THAT field to be the Primary Key (PK) field.
Make "TransferSchool.StudentID" a Number (Long Integer type) field. Then you should be able to set the relationship.


...........PK.(the one)................................... FK (the many)
TransferStudent.StudentID ----------->> TransferSchool.StudentID

or if this helps
(TransferStudent.StudentID_PK ----------->> TransferSchool.StudentID_FK)

[cunning]

Thank you for your reply. I am looking into making a foreign key but it asks me to ensure StudentID (TansferSchool Table) has a data type short text so I can't change it a Number data type. Other rule is to set it up as a one-to-one relationship.
&

[cunning]

Thanks for the reply. & Yes. the record for Transfer Students table is 302 & Transfer students only has 41. I have to create a relationship between the two StudentID. (Transfer School being primary). I'll try the unmatched query.

[ssanfu]

If now sounds like the "StudentID" fields (both) are text fields. Try NOT having the "StudentID" fields as the PK field. Then like the fields.

[cunning]

Thank you for posting again, helped me gain more knowledge about PK & how it functions. Unfortunately the book contained an error.
I was told to just create the relationship the opposite way. (Transfer school -> Transfer Student) which I knew it worked but the query does not contain all the records so that's what threw me off.
Thanks again.