Referential integrity / corruption

[Richio]

I have a db (unsplit with approx 10 users) it keeps corrupting but I am looking into why.

When it does corrupt sometimes I seem to lose data and it messes with the table relationships (ie it deletes some of them)

When I re enter the relationship and try to enforce the ref integrity I am unable as some data has been lost / deleted from the primary table.

My question is. Is there an easy way to compare the linked data in one table to another to find out what is missing. I have thousands of entries in each with a one to many relationship and it is near on impossible to find the data in the secondary table which is not in the primary

Any ideas would be appreciated

 

[charityg]

Select distinct Table1ID from Table2 where Table1ID not in (Select Table1ID from Table1)

In other words,
Select the Distinct ForeignKey from the Many table that doesn't exist as a PrimaryKey in the One table.

 

[Pat Hartman]

Use the find unmatched wizard to create a query.