Referential Integrity (1 Viewer)

[kevnaff]

Hello All.

The database I have taken over has two tables called LookupSupplier and LookupManufacturer. Having been in charge of the database for a year or so, I can't see any instance why there needs to be two different tables, when a lot of the data is the same. I have created a new table called LookupCompany, combined the data from the two tables and removed the duplicates.

I am currently attempting to delete all of the relationships relating to the two redundant tables, and create new identical relationships with the LookupCompany table.

However when I am doing so, I get the following message:

I am trying to create a one to many relationship between the LookupCompany table and the Contracts table in my database. The relationship links the primary key CompanyName to the Contractor field.

Having gotten this message, I have been through all instances of Contractor in the Contracts table and verified that they all exist in the CompanyName field.

I am struggling to understand why it won't let me tick the referential integrity box.

Has anybody seen this before?

Thanks

 

[Galaxiom]

It would be more usual to have a numeric code as the key in the Lookup rather than CompanyName and the ID stored as Contractor.

Are you sure you are not looking at the Contracts table with a lookup on Contractor field that displays the name? Check the properties of the Contractor field.

 

[kevnaff]

It would be more usual to have a numeric code as the key in the Lookup rather than CompanyName and the ID stored as Contractor.

Are you sure you are not looking at the Contracts table with a lookup on Contractor field that displays the name? Check the properties of the Contractor field.

Hi Galaxiom.

Both the old LookupSupplier and LookupManufacturer tables used the name rather than an ID for their primary key, so I continued this with the LookupCompany table.

I have created a copy of the backend, and deleted all records in the Contracts table except for one, and I am now able to create the relationship with referential integrity. This suggests that there is an issue with a field name, I think.

As I said, I exported all records in to Excel of Contractor from Contracts, and combined this with all records of CompanyName. All instances of Contractor exist in the CompanyName field.

I was expecting to see a value in the Contractor missing from the CompanyName.

 

[The_Doc_Man]

The only thing I would look for (with regard to two apparently very similar tables) is something that you would be able to answer but I can't 'cause I don't know the business flow. I can see a case where the manufacturer might be different that the supplier if your supplier is a wholesaler/reseller. I once worked for a Value-Added-Reseller, or VAR. We were authorized to add value to Digital Equipment Corp. computers but there was no reason the customer couldn't buy supplies directly from DEC later on. But only you would know whether that situation applies to your company.

 

[strive4peace]

@kevnaff, to enforce referential integrity, the field in the parent table must have a Unique Index. Normally it is customary to use the primary key (ContactID), not a text field. That also has much better performance if ContactID is an AutoNumber (Long Integer)..

If you use a numberic foreign key, be sure to remove the Default Value of 0 (zero) that Access automatically assigns.

To find records in the related table that don't exist in the main table, make a query using both tables. Change the relationship to show all records from the related table. On the grid, in criteria under that main table field:
IS NULL

and then show whatever other fields you want to see

 

[Pat Hartman]

To find the bad records you need two queries. Use the wizard to make a find unmatched for both the Supplier and the Manufacturer. I assume the table has the two columns.

Then, in the Relationship window, add a second instance of your new table. Join Supplier to the first instance and Manufacturer to the second instance. This isn't duplicating the table or any data, it is isolating the relationship. This may be what is causing your error.

 

[gemma-the-husky]

How did you determine the duplicates that you deleted?
Maybe what appeared to be duplicates weren't true duplicates. You often can't do this by visual inspection. There may be trailing spaces, non-printing characters, and characters that look like spaces which aren't actually spaces. There are two sorts of apostrophe. A double quote and 2 single quotes together may look the same.

Anyway, You should be able to establish which looked up suppliers, and which looked up manufacturers are failing, using an unmatched query.

do a query joining contracts.contractor to lookupcompany.companyname, set it as a left join (or right join, I am not sure which is the technical term), and add a criteria of null for the company name.

This will find the orphan contractors.

When you have done this, seriously think of adding a numeric key to the lookup table, and change all the cross references to be numeric, as already suggested. Yes, it might seem to be a lot of work, but it's a lot easier and more efficient going forward. It also means you can change company names without needing cascading updates of linked data.

Maybe you predecessor tried the same exercise, and shelved it!