how to find blind records 2

[davikokar]

Hallo,
I have two linked tables with around 10 thousands records. I want to enforce referential integrity but it's not possible because there are some records in one table that don't have any reference to the other table. Is there a way to automatically find out which one are?
thanks

 

[KenReay]

Hi

Make a select query joining the two tables on the common column(s)

Right click the join line, modify the join type by selectiong one of the three options presented, so as to select all rows from the 'main' table

drag the common column(s) from this table into the design grid and set their criteria to Is Null

drag other columns as required so you can identify the rows

run the query

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[willir]

Alternate solution...

[tt]
SELECT Table1.PrimaryKeyField, Table1.YourJoinField1 from Table1
WHERE Table1.YourJoinField1 NOT IN
(SELECT Table2.YourLinkField1 from Table2);
[/tt]

You can even use a variation of this to delete the compromised data.

Richard

 

[davikokar]

thanks to both for the valuable posts