Simple Delete query syntax help needed.

[Edward_]

I had a full question prepared showing my non-working delete query prepared, but I think it will be simpler to just show a working select query I have that selects the records I want to delete instead.



My working Select query:

SELECT [Order Details].[Product ID], [Order Details].[Order ID], Products.[Product ID], Products.[Product Name], Products.[Units In Stock]FROM [Order Details] RIGHT JOIN Products ON [Order Details].[Product ID] = Products.[Product ID]
WHERE ((([Order Details].[Order ID]) Is Null) AND ((Products.[Product ID]) Like "VD-*") AND ((Products.[Units In Stock])=0))
WITH OWNERACCESS OPTION;

What I can't seem to do is convert the above SELECT to the delete sequence of: Delete, From, Where

I simply want the new delete query to delete the records found in the Products query only, using the above criteria.

Any advice greatly appreciated.

[RuralGuy]

Just so you understand that select queries don't always work as Action queries. Select queries are read only queries and Action queries require writing to the Recordset. You can read an ambiguous query but not write to the same.
That being said...maybe some query guru will jump in with a solution for you.

[Minty]

You have two tables in the select statement. A delete can only work on one table at a time.

 DELETE  [Order Details].*
FROM [Order Details] RIGHT JOIN Products ON [Order Details].[Product ID] = Products.[Product ID] 
WHERE ((([Order Details].[Order ID]) Is Null) AND ((Products.[Product ID]) Like "VD-*") AND ((Products.[Units In Stock])=0))
WITH OWNERACCESS OPTION;

Make sure you have a back up, and test that this is doing what you want.

[orange]

Can you tell us the "business rationale" (simple English) behind this request?
How can the OrderDetails have a NULL Order ID?
You want to delete the OrderDetails, or ????

Can you show us your relationships window as a jpg?

[Edward_]

Can you tell us the "business rationale" (simple English) behind this request?
How can the OrderDetails have a NULL Order ID?
You want to delete the OrderDetails, or ????

My rationale behind this is to reduce db size by eliminating countless unused legacy product records.
My Order Details will have a Null OrderID if no Order detail exists containing a certain product ID(no order has the Product ID in question)
(this selection method is acceptable in my Select query.)

You have two tables in the select statement. A delete can only work on one table at a time.

Is this true If I'm basing the criteria on Table-1, but deleting only from Table-2 also?
I am wanting to delete from only One Table.

Attachment 32426

Edit:

This is my latest attempt:

DELETE Products.*
FROM Products RIGHT JOIN [Order Details] ON Products.[Product ID] = [Order Details].[Product ID]
WHERE ((([Order Details].[Order ID]) Is Null) AND ((Products.[Product ID]) Like "VD-*") AND ((Products.[Units In Stock])=0))
WITH OWNERACCESS OPTION;

Result:
'Could not delete from specified tables'

[Minty]

Have you tried simply using the query builder - get your select correct, and then use the query builder to change the query to a Delete query?
Or using the ID and an IN clause to find the id's based on your current select query.