Delete query

[peljo via AccessMonster.com]

I have the following query which i like to convert into a delete query and
use the command
Dim StrDelete As String
DoCmd.RunSQL StrDelete
Can you help me ?

My query is the following :

SELECT [order details].OrderID, products.grade, [order details].ProductID,
[order details].cartons, [order details].Quantity, [order details].liters,
products.size, products.branch0, products.items0, orders.customerid,
Customers.Customerid FROM (([order details] INNER JOIN products ON [order
details].ProductID = products.Productid) INNER JOIN orders ON [order details].
OrderID = orders.orderid) INNER JOIN Customers ON orders.customerid =
Customers.Customerid
WHERE ((([order details].OrderID)=(SELECT Max([orderid]) FROM orders)))ORDER
BY products.grade;

 

[John Spencer]

You can only delete from one table at a time - unless you have a
relationship built and have set cascade delete for the relationship. In
that case you still tell the program to delete from one table and in the
background the database handles deleting the dependent records.

Assuming you want to delete the Order Details your query would probably be
something like

DELETE[order details].OrderID
FROM (([order details] INNER JOIN products ON
[order details].ProductID = products.Productid) INNER JOIN
orders ON [order details].OrderID = orders.orderid) INNER JOIN
Customers ON orders.customerid =Customers.Customerid
WHERE ((([order details].OrderID)=
(SELECT Max([orderid]) FROM orders)))

If you wanted to delete the Order after you delete the details then use
DELETE[orders].OrderID
FROM (([order details] INNER JOIN products ON
[order details].ProductID = products.Productid) INNER JOIN
orders ON [order details].OrderID = orders.orderid) INNER JOIN
Customers ON orders.customerid =Customers.Customerid
WHERE ((([order details].OrderID)=
(SELECT Max([orderid]) FROM orders)))

If you have a relationship set up between Orders and Order Details AND have
cascade deletes set, then you would only need the second query to delete the
order and the order details