Delete non matching records

[Guest]

I have two tables, that are linked (typical header/detail).

Under certain situations, I can have entries in the detail record that
aren't matched in the header (app blows out)

I would like to run a "housekeeping" query on form startup to look for
orphan detail records and delete them

I don't see how I can do this via QBF, and my SQL is not strong.

Anyone have any suggestions?

Thx

 

[Allen Browne]

Use a subquery in the WHERE clause:
DELETE FROM Table1
WHERE NOT EXISTS
(SELECT ForeignID
FROM Table2
WHERE Table2.ForeignID = Table1.ID);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[John Spencer]

Also, you can use the unmatched query wizard to build a query to display the
relevant records and then turn that into a delete query.