Referential intergrity problem 1

[SpandexBobcat]

Hi gang,

I am trying to inforce the referential intergrity to my table relationships but it wont allow it. There are violations in the rules... The tables are already populated and an example of what I am trying to do is link the customer_no in the Customer table (primary key) with the customer_no in the contracts table. I have looked through both tables, the customer one has consecutive auto-numbers so there isnt a problem there (I hope) On looking at the contracts table some entries for the customer_no were blank. On deleting these entries it still wouldn't do it and I thought it would... If I delete evey record from the tables it does work but then I have lost my data and cant copy it back in! Any ideas???

Simon

 

[SpandexBobcat]

Could an example of the problem be the fact that in the Contracts table, customer_no 443 has a number of different names (obviously they changed their name ove the years but on each occation a new record has not been created, only modified entry no 443...) If this is the case what the hell do I do???

The reason I need to have this all working is that on the view Contracts form I want to be able to have all the customer info shown re that particular customer but pull it from my Customer table and not have the information duplicated... it all makes sence but how do I do it???

 

[lespaul]

Try this:

Create a query:

SELECT * FROM CONTRACTS WHERE CUSTOMERID NOT IN (SELECT CUSTOMERID FROM CUSTOMERS)

You may have contracts with customerid's that don't exist in the customer table. If so this query will identify which records in contract have no related information in customer.


Leslie

 

[SpandexBobcat]

Thanks for the suggetion Leslie.

Sorry, I am not an expert at this, the query you are suggesting, I need both Customer and Contract tables present in the query right? What should the code be in the criteria field and for what table???

Simon

 

[lespaul]

In the top right corner of the Access query grid, there's a button that you can change the view of the query from the grid, to the dataset or to SQL. change the view to SQL, paste the statement above into the SQL editor and then change the view to the datasheet to view the results.

Leslie

 

[SpandexBobcat]

Thank you very much for your prompt and very accurate responce! It worked wonderfully and after deleting the bogus entries it worked!

I was wondering if you would be able to assist me in the next problem, when viewing a contract record (containing customer_no, name, contact info etc) I also want to show the full address details that are stored in the Customer table... Now I have sucessfully created the link between these two tables it should be easy. I tried setting the recor source to the CUST.[LineAddr1] but it didnt work... If you want to leave it at what you ahve already helped out with just say so... I should start a new thread but.......

Simon

 

[SpandexBobcat]

I solved it by looking at another thread. Thanks for your help though

Simon