No Referential Integrity

[KristenD]

I am currently working in A2007 and am tinkering with my db with help from other forum members here and I was implementing some of the suggestions. I added a table to the db to hold look up data and when I went into the the relationships to drag and drop to create the relationship it pops up an error stating that it cannot create referental integrity. See the attachment below.

I checked and double checked the data types are the same. What am I doing wrong?

Thank you!

 

[Beetle]

Is there existing data in the table that you are trying to establish the relationship with? If so you probably have values in that FK field that do not match any of the PK values in your lookup table.

 

[KristenD]

It is all the same information so they should match. They are both number fields using their ID's so for example 5841 is in the tblEmpRating under SuptID already and I put it in the tblSuperintendent under SuperIDPK as 5841 and it is like that for all the ID's. That is where I pulled the info from.

 

[jdraw]

Can you post a jpg of your tables and relationships?
Can you tell us what the PK is in each table?

Please tell us more about

It is all the same information so they should match.

 

[KristenD]

The PK for tblSuperintendent is SuptID and the PK for tblEmpRating is EmpRatingID.

I have attached the relationships report.

Thank you!

 

[Beetle]

Try running an unmatched query on those tables using the PK field in tblSuperIntendent and the FK field in tblEmpRating.

 

[KristenD]

That worked...now I'm working on fixing the keying mistakes. Thank you!!

 

[jdraw]

tblEmp has employee info
tblRating has rating info (doesn't need/shouldn't have empRatingId )
tblEmpRating has Employee'sRating info (shouldn't have suptName)

These are some of the issues with structure.

In tblEmpRating the link should go to tblRating RatingId