Referential Integrity -- Unable to Enforce

[Pianolady]

I run my business in an Access Database that I built about 20 years ago -- I am now working in Access 2003. I had enforced referential integrity in all relationships so that everything would relate to a customer and a product and would get invoiced out. All of a sudden I was missing things that were not getting invoiced and found out that Referential Integrity was no longer enforced. This was about one year ago and I have spent HOURS AND HOURS AND HOURS trying to enforce referential integrity again between two of the tables. It tells me there is a record that violates referential integrity rules. I have gone record by record (almost 16,000) more than once and cannot find the "offending" record(s). HELP!! I am losing money because if something is mis-typed it is not invoicing and it is not telling us that the item does not exist in the parent table. What can I do to find the "orphan" or...??? Pleaseeee help. Thank you so much in advance!

 

[RainLover]

Welcome to the Forum.

Asking questions here won't get the coverage you want.

I will ask a Moderator to move it to a more appropiate section.

Enjoy your stay.

 

[RainLover]

To answer your question.

Create a query with the two tables in question and join by the Primary Key.

Adjust your join by right clicking on the join line in the query.

Use Number two. If this does not show what you want then use number three.

Hope this makes sence. If not ask for more clarification.

 

[Pianolady]

Thanks Rainlover... since I'm barely computer literate and had help building this database any clarification you can give me would be MOST appreciated Have a beautiful Easter!!

 

[RainLover]

Do you need clarification on any particular point.

If so please ask your question.

 

[gemma-the-husky]

you have a record in the child (many) table, that is not represented by a matching record in the parent (one) table. so you cannot set RI.

create a query using the wizard, use an unmatched query. follow the prompts. select the relevant fields from the child and parent table. the wizard will construct the query, which will identify the problem records

this is what rainlover is describing. - constructing an unmatched query, but doing it manually, instead of with the wizard

if you examine the query, you will see how it is done, as you can do this manually, and you do not have to use the wizard. In your case, try the wizard till you understand it

 

[Pianolady]

THANK YOU THANK YOU THANK YOU to RainLover and Gemma-the-Husky! My problem is fixed!! You saved me and I cannot thank you enough. Have a most blessed, wonderful week and thanks again!!!
Dianne

 

[RainLover]

Me thinks Dianne is a happy little Vegemite.

 

[Pianolady]

You are sooo correct RainLover!

 

[RainLover]

More Happy little Vegemites.

http://www.youtube.com/watch?v=0yA98MujNeM

 

[gemma-the-husky]

I am sure we are both pleased you were able to get this resolved

all these things are little tricks that once learned, are not forgotten. then it doesn't take hours the next time you get a similar issue.

note that you often can use the results of an unmatched query, as the basis for an append query, to add the missing records into the parent table - avoiding the need to add them manually

 

[Pianolady]

Thanks again for another tidbit of genius gemma-the-husky! And thanks for the wonderful little vegemites RainLover!