Solved

Troubleshooting referential integrity

Posted on 2008-06-17
3
195 Views
Last Modified: 2013-11-28
Table A                                      Table B
pkFieldOne     Text     1>>>M    fkFieldOne     Text
pkFieldTwo    Test      1>>>M   fkFiledTwo     Text
9000 records              7000 records

Not all records need to reside in Table B   just all 7000 records need to relate to Table A
Access 2000/2003   recognizes the 1 to many relatiionship but it won't allow the setting of RI

I have ran two unmatching fields  One where pkFieldOne = fkFieldOne  and the other where pkFieldTwo = fkFieldTwo  fix the problems then tried to set RI and it still will not set.  I think I have unmatching groupings of fieldOnes and fieldTwos

I found this problem by the ability of adding data into TableB without any error messages.   This messes things up.  
How do I run one unmatching field query where it pkFieldOne = fkFieldOne  AND pkFieldTwo = fkFieldTwo?
0
Comment
Question by:MG_Janiszewski
3 Comments
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 21808732
OK, as a query, go into SQL view and paste the following

Select b.fieldone,b.fieldtwo from tableb b
left outer join tablea a on b.fieldone = a.fieldone and b.fieldtwo = a.fieldtwo
where a.fieldone is NULL;


basically what we are saying is from tableb join to tablea but (using left outer) we do not care if tablea has matching records. The "where" will select those rows where there is no tablea row that matches (because it will be NULL)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21816200
MG_Janiszewski,

Just curious,

Why do you have two primary keys in the same table linked to two of the same foreign keys in a second table?

JeffCoachman
0
 

Author Comment

by:MG_Janiszewski
ID: 21818132
The whole database is centered around fieldOne and FieldTwo.  TableA is the Master table and Table B breaks additional information about the data in Table A.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now