Error 3086

[Guest]

I made a delete query but get the Error 3086. Following the discussions here
I added the "DISTINCTROW" but still I get the error message.

the query is as follows:
DELETE DISTINCTROW OrdersReceived.*, OrdersReceivedSubQuery1.ClinicID,
OrdersReceivedSubQuery1.OrderID, OrdersReceivedSubQuery1.SupplierID,
OrdersReceivedSubQuery1.OrderDate
FROM OrdersReceived LEFT JOIN OrdersReceivedSubQuery1 ON
(OrdersReceived.SupplierID = OrdersReceivedSubQuery1.SupplierID) AND
(OrdersReceived.ClinicID = OrdersReceivedSubQuery1.ClinicID) AND
(OrdersReceived.OrderID = OrdersReceivedSubQuery1.OrderID) AND
(OrdersReceived.OrderDate = OrdersReceivedSubQuery1.OrderDate)
WHERE (((OrdersReceivedSubQuery1.) Is Null) AND
((OrdersReceivedSubQuery1.OrderID) Is Null) AND
((OrdersReceivedSubQuery1.SupplierID) Is Null) AND
((OrdersReceivedSubQuery1.OrderDate) Is Null));

What am I doing wrong here?
LVER

 

[Guest]

I expect this isn't an updateable query. If you change this to a select
query, can you even edit any field from the OrdersReceived table?

This is the type of query that might work on SQL Server but Access can't
handle. You might be able to create a work-around by using temporary tables
with the proper primary and foreign keys created.

 

[John W. Vinson]

What am I doing wrong here?

Probably trying to delete from both tables. Try removing one table's fields
from the DELETE clause:

DELETE DISTINCTROW OrdersReceived.*
FROM OrdersReceived LEFT JOIN OrdersReceivedSubQuery1 ON
(OrdersReceived.SupplierID = OrdersReceivedSubQuery1.SupplierID) AND
(OrdersReceived.ClinicID = OrdersReceivedSubQuery1.ClinicID) AND
(OrdersReceived.OrderID = OrdersReceivedSubQuery1.OrderID) AND
(OrdersReceived.OrderDate = OrdersReceivedSubQuery1.OrderDate)
WHERE (((OrdersReceivedSubQuery1.) Is Null) AND
((OrdersReceivedSubQuery1.OrderID) Is Null) AND
((OrdersReceivedSubQuery1.SupplierID) Is Null) AND
((OrdersReceivedSubQuery1.OrderDate) Is Null));

if your intention is to delete records from the OrdersReceived table.

If the OrdersReceivedSubQuery1 is not updateable, though, this query won't be
either. You may need to use a NOT EXISTS clause instead:

DELETE DISTINCTROW OrdersReceived.*
FROM OrdersReceived
WHERE NOT EXISTS(SELECT OrdersReceivedSubQuery1.SupplierID FROM
OrdersReceivedSubQuery1 WHERE
(OrdersReceived.SupplierID = OrdersReceivedSubQuery1.SupplierID) AND
(OrdersReceived.ClinicID = OrdersReceivedSubQuery1.ClinicID) AND
(OrdersReceived.OrderID = OrdersReceivedSubQuery1.OrderID) AND
(OrdersReceived.OrderDate = OrdersReceivedSubQuery1.OrderDate));


John W. Vinson [MVP]

 

[John Spencer]

One you are trying to delete from two tables at one time.
Two you have a mysterious reference in the where clause to
"OrdersReceivedSubQuery." << note the period>>
As a guess you probably should be able to use

DELETE DISTINCTROW OrdersReceived.*
FROM OrdersReceived LEFT JOIN OrdersReceivedSubQuery1 ON
(OrdersReceived.SupplierID = OrdersReceivedSubQuery1.SupplierID) AND
(OrdersReceived.ClinicID = OrdersReceivedSubQuery1.ClinicID) AND
(OrdersReceived.OrderID = OrdersReceivedSubQuery1.OrderID) AND
(OrdersReceived.OrderDate = OrdersReceivedSubQuery1.OrderDate)
WHERE OrdersReceivedSubQuery1.ClinicID Is Null AND
OrdersReceivedSubQuery1.OrderID Is Null AND
OrdersReceivedSubQuery1.SupplierID Is Null AND
OrdersReceivedSubQuery1.OrderDate Is Null

If that fails then you will need to build a more complex query using a
subquery in a where clause to identify the records to delete. Does
OrdersReceived have a primary key field?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

The intent of the delete query is as folows:
OrdersReceivedSubQuery1 just selects Id's form different tabels. The delete
query on the other hand is supposed to delete the records in the table
OrdersReceived where there is no responding supplierID AND no responding
ClinicID and no responding OrderID and no responding date available in
OrdersReceivedSubQuery1. (For some reason the ClinicID in the WHERE clause
vanished when I pasted the query in the question.)

So I am deleting from a table after having compaired the data in the table
with the data in a selection query.

Duane, you are wright: As a selectquery I get some records but when I try to
delete them, it won't let me.

I am a self thought access layman, please be gentil.

Thank you (and all other responders) for your help,

LVer

 

[Guest]

Thanks a lot, John. This realy did it. I do not understand though why this
one worked and not the query that I used. It still is deleting from the same
table using a query to find the data to be deleted?