Delete main record that has no subrecords

[mcdowdjr]

I am having trouble running a delete query on the main records that
have no subrecords. Here is what I have. I have tblOrder with OrderID,
in a related tblTransaction I have all the items the customer orders in
detail. With a foreign key of OrderID. I am trying to run a delete
query that will delete the primary order record since it does not have
a matching transaction record. Thanks for any help..

-Josh

 

[Jeff L]

Delete from tblOrder
Where OrderId Not In (Select OrderID From tblTransaction)

Hope that helps!

 

[Guest]

In your delete query, add both tables and a join on the related column. Add
the OrderId column from tblTransaction and in the criteria for this, put:

Is Null

This should return records from the orders table that have no records in the
transaction table.

Barry

 

[mcdowdjr]

Thanks! That makes sense. However, I'm getting a syntax error with the
from clause.

 

[Allen Browne]

This should do it:
DELETE FROM tblOrder
WHERE NOT EXISTS
(SELECT TransactionID FROM tblTransaction
WHERE tblTransaction.OrderID = tblOrder.OrderID);

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

 

[mcdowdjr]

Here is what I have put. It gives me a syntax error on the Tbl Order in
the first line.

DELETE FROM Order
WHERE NOT EXISTS (SELECT TransactionID FROM Transaction WHERE
Transaction.OrderID = Order.OrderID);

any help would be great, by the way I am using Access 2000 if that
makes a difference.
Thanks

 

[Allen Browne]

That might be due to the fact that ORDER is a reserved word in JET queries.

 

[mcdowdjr]

Changing the name worked. Thank you all!

That might be due to the fact that ORDER is a reserved word in JET queries.