Delete Orphans Query

[Guest]

Hi

I have two tables, tblOrders with 2247 records and tblOrderDetails with
30573 records. The tables are NOT linked at present. Both tables have a Long
Integer field called OrderID (Unique Index in tblOrders). Using the Unmatched
Query Wizard I have identified 27081 records in tblOrderDetails that do not
have corresponding records in tblOrders (just don't ask me how). Is there any
simple way, say by using a Delete Query, to delete these orphan records
without having to write a load of VBA code. Access will not let me delete the
list that the query returns.

This is the query :-

SELECT tblOrderDetails.ID
FROM tblOrderDetails LEFT JOIN tblOrders ON tblOrderDetails.OrderID =
tblOrders.OrderID
WHERE (((tblOrders.OrderID ) Is Null));

 

[Douglas J. Steele]

DELETE FROM tblOrderDetails
WHERE tblOrderDetails.OrderID NOT IN
(SELECT DISTINCT OrderID FROM tblOrders)

 

[Allen Browne]

This subquery should identify the records in tblOrderDetails that have no
matching OrderID value in tblOrders:

DELETE FROM tblOrderDetails
WHERE NOT EXISTS
(SELECT OrderID FROM tblOrders
WHERE tblOrders.OrderID = tblOrderDetails.OrderID);

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

Presumably you want to create the relation with RI once this is cleaned up.
Can I also suggest that you set the Required property to Yes for OrderID in
tblOrderDetails, and remove the zero from its Default Value property. The
zero is often the source of these orphans, and the Required property is
needed even with RI.

 

[Guest]

Thanks very much Douglas and Allen for some useful information. Tables now
linked OK.