If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Unable to create relationship
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. -- Ann Scharpf |
#2
|
|||
|
|||
Unable to create relationship
Is the linking field on the "one" side a PK or at least unique?
|
#3
|
|||
|
|||
Unable to create relationship
Yes, [ContractorLaborCategories].[CSCLabCatCode] is set as Indexed (No
Duplicates). -- Ann Scharpf "Fred" wrote: Is the linking field on the "one" side a PK or at least unique? |
#4
|
|||
|
|||
Unable to create relationship
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? |
#5
|
|||
|
|||
Unable to create relationship
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 http://I.Am/DougSteele (no private e-mails, please) "Ann Scharpf" wrote in message ... 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. -- Ann Scharpf |
#6
|
|||
|
|||
Unable to create relationship
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 "Douglas J. Steele" wrote: 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 http://I.Am/DougSteele (no private e-mails, please) "Ann Scharpf" wrote in message ... 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. -- Ann Scharpf |
Thread Tools | |
Display Modes | |
|
|