I have a table that has user names and they are associated to department.

I am trying to use a relationship between the two tables

the states table has
ID and Department Name

when I try to relate the 2 tables I get an error message
I have attached it here.

You are enforcing referential integ on orphaned records. Say you have a one-to-many relationship, if you have the many but not the one in the relationship, then you get this error.

I have a table that has user names and they are associated to department.

So what i would have here is 2 tables.

tblUserNames

UserK (autonum) - primary key
UserFName (text)
UserLName (text)
DeptK (number)

tblDepts

DeptK (autonum) - primary key
DeptName (text)

Now link the tblDepts DeptK (primary Key) to the tblUserNames DeptK (foreign key).
Let me know if this resolves the issue.

I'm not a real expert on relationships, but this is because with referential integrity the records in the parent and child table must match. If there are any that don't match at all this will prevent you from setting RI in your relationships.

If you've not got many records, one way is to bring up both tables next to each other and just check or you can do an unmatched query using the wizard.

Hope this is clear - any more questions, post back.

would blank records give me a problem????


Now link the tblDepts DeptK (primary Key) to the tblUserNames DeptK (foreign key).
Let me know if this resolves the issue.


this I know would work but I was trying to avoid this right now.

Blank records could be a problem I think if the default value is set to 0. You need to make sure they're set to Null.

how would I set them to null. just put a blank???

No you do this in the table design. In the properties section at the bottom, on the General tab, there's a Default Value space.