Unable to create relationship

[Ann Scharpf]

I am trying to create a relationship between two tables with referential
integrity. I'm trying to link on the following fields:

[ContractorLaborCategories].[CSCLabCatCode]
[Employees].[LabCat]

When I try to create, I get an error message that says "Data in the table
'Employees' violates referential integrity rules.

YET, when I create an unmatched query, I cannot find the offending
record(s). Here's the SQL for my query.

SELECT Employees.Employee, Employees.LabCat,
ContractorLaborCategories.CSCLabCatCode
FROM Employees INNER JOIN ContractorLaborCategories ON Employees.LabCat =
ContractorLaborCategories.CSCLabCatCode
WHERE (((ContractorLaborCategories.CSCLabCatCode) Is Null));

Can anyone help me to figure out what is the problem in my Employees table
that's preventing me from creating this relationship?

Thanks very much.

 

[Fred]

Is the linking field on the "one" side a PK or at least unique?

 

[Ann Scharpf]

Yes, [ContractorLaborCategories].[CSCLabCatCode] is set as Indexed (No
Duplicates).

 

[Fred]

I ask dumb questions but sometimes they are useful......

- Field types for the linking fields the same (or are one of the OK
exceptions?)

(I'm not fluent enough on SQL to check yours)

Side note: It's hard to guess which side is the "many" by hose table names.
Unless one of them is actually instances of an employee doing/using a labor
type (vs. the namesake of the table) it's hard to see "one to many"
possibilities there.

- Low tech idea....make a query that displays the linked records and look
for blanks on side that should always have a record.

- Any chance you got the tables reversed in the integrity criteria?

 

[Douglas J. Steele]

You'll never get Null values for ContractorLaborCategories.CSCLabCatCode
using that SQL: it only returns rows where the fields match.

Try using a Left Join instead:

SELECT Employees.Employee, Employees.LabCat,
ContractorLaborCategories.CSCLabCatCode
FROM Employees LEFT JOIN ContractorLaborCategories ON Employees.LabCat =
ContractorLaborCategories.CSCLabCatCode
WHERE (((ContractorLaborCategories.CSCLabCatCode) Is Null));

(I suspect you didn't actually use the Find Unmatched Query Wizard to
generate your query!)

 

[Ann Scharpf]

Right you are! I had tried the unmatched query wizard before but I think I'd
inadvertently clicked the wrong field for one of the tables. Then I saw
thispre-existing query that I thought would do what I wanted. I re-did the
unmatched query with the wizard and found the bad records.

Sorry to waste your time on a dumb user error!
--
Ann Scharpf

You'll never get Null values for ContractorLaborCategories.CSCLabCatCode
using that SQL: it only returns rows where the fields match.

Try using a Left Join instead:

SELECT Employees.Employee, Employees.LabCat,
ContractorLaborCategories.CSCLabCatCode
FROM Employees LEFT JOIN ContractorLaborCategories ON Employees.LabCat =
ContractorLaborCategories.CSCLabCatCode
WHERE (((ContractorLaborCategories.CSCLabCatCode) Is Null));

(I suspect you didn't actually use the Find Unmatched Query Wizard to
generate your query!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am trying to create a relationship between two tables with referential
integrity. I'm trying to link on the following fields:

[ContractorLaborCategories].[CSCLabCatCode]
[Employees].[LabCat]

When I try to create, I get an error message that says "Data in the table
'Employees' violates referential integrity rules.

YET, when I create an unmatched query, I cannot find the offending
record(s). Here's the SQL for my query.

SELECT Employees.Employee, Employees.LabCat,
ContractorLaborCategories.CSCLabCatCode
FROM Employees INNER JOIN ContractorLaborCategories ON Employees.LabCat =
ContractorLaborCategories.CSCLabCatCode
WHERE (((ContractorLaborCategories.CSCLabCatCode) Is Null));

Can anyone help me to figure out what is the problem in my Employees table
that's preventing me from creating this relationship?

Thanks very much.