Delete query problems

[lgbatdw]

I want to delete inventory records that have no qty in stock and no qty on backorder and are not present in 4 other tables (Sales Orders, Invoices, Quotes & PO's)- basically unused inventory item recs. This is the delete qry I'm trying to use, but get the error "Could not delete from specified tables.". First of all, I only want to delete from one table - "Inventory". Help points to permissions/read-only issues, but since I can run another qry to delete from this table based on different criteria, I think this is not the case. I think my query is not quite right, but don't know what needs to be changed. I'm using Access 2003.
Thanks for any help in advance.


DELETE Inventory.*
FROM (((Inventory LEFT JOIN [Invoice Lineitems] ON Inventory.[Item Number]=[Invoice Lineitems].[Item Number]) LEFT JOIN [Sales Order Lineitems] ON Inventory.[Item Number]=[Sales Order Lineitems].[Item Number]) LEFT JOIN [Purchase Order Lineitems] ON Inventory.[Item Number]=[Purchase Order Lineitems].[Item Number]) LEFT JOIN [Quotation Lineitems] ON Inventory.[Item Number]=[Quotation Lineitems].[Item Number]
WHERE (((Inventory.[Quantity in Stock])=0) AND ((Inventory.[Quantity on Back Order])=0) AND (([Invoice Lineitems].[Invoice Number]) Is Null) AND (([Sales Order Lineitems].[Sales Order Number]) Is Null) AND (([Purchase Order Lineitems].[Item Number]) Is Null) AND (([Quotation Lineitems].[Item Number]) Is Null));


Linda in MN

 

[PHV]

Perhaps this ?
DELETE * FROM Inventory
WHERE [Quantity in Stock]=0 AND [Quantity on Back Order]=0
AND [Item Number] Not In (SELECT [Item Number] FROM [Invoice Lineitems])
AND [Item Number] Not In (SELECT [Item Number] FROM [Sales Order Lineitems])
AND [Item Number] Not In (SELECT [Item Number] FROM [Purchase Order Lineitems])
AND [Item Number] Not In (SELECT [Item Number] FROM [Quotation Lineitems])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lgbatdw]

Thanks PH for the VERY prompt reply. Well, I tried the code you supplied and didn't get the message noted above, but it took forever to run (appears to be hung up) and finally returned zero records and I know there should be 1678 returned. In trying to do some troubleshooting, I changed the code eliminating all criteria except the following "Not In (SELECT [Item Number] FROM [Invoice Lineitems])" and it still took about 4 minutes and returned zero records. There are only 5022 inventory records total. If I remove the word "Not" from the clause, it runs quickly. I think it doesn't like the "Not" clause. Any thoughts?

Linda in MN

 

[PHV]

And what about this ?
DELETE * FROM Inventory
WHERE [Quantity in Stock]=0 AND [Quantity on Back Order]=0
AND Not ([Item Number] In (SELECT [Item Number] FROM [Invoice Lineitems]))
AND Not ([Item Number] In (SELECT [Item Number] FROM [Sales Order Lineitems]))
AND Not ([Item Number] In (SELECT [Item Number] FROM [Purchase Order Lineitems]))
AND Not ([Item Number] In (SELECT [Item Number] FROM [Quotation Lineitems]))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886