How to find Orphan Records ?

  • Thread starter Thread starter Will
  • Start date Start date
W

Will

If you have a dB which does not have referential integrity on...

And since when linking tables you can't see 'all records in both' (at least
I don't know how to do it)

How can you set up a query or whatever to see Orphan records?

thanks for any help.
 
Will,
Create a query between your Parent and Child table related via your key
field, but make the join a "Right-Outer" join.
Which means... "show all in my child table and only those in the parent that
match."
When the query is run, any child key fields that don't show a corresponding
parent key field are orphans.

Of course, you are going to get the standard lecture on proper table design.
Referential Integrity combined with Cascading Updates/Deletes will solve
this problem permanently.
 
Use the Unmatched Query wizard (first dialog when you create a new query) to
select the records in one table that have no match in the other.
 
There's a Find Unmatched Query Wizard that you can use. Just follow its
instructions.
 
Back
Top