Delete query and "Could not delete from specified Table"

[BFish via AccessMonster.com]

I'm getting the "Could not delete" message when trying to run the query below.
I assume do to the non updatable status.

Would anyone know how I can modify the query or a work around.

DELETE tblToWholesalePreApnd.*
FROM qselMatchAcctInvByAddressTrimPre INNER JOIN tblToWholesalePreApnd ON
qselMatchAcctInvByAddressTrimPre.InvNo = tblToWholesalePreApnd.InvNo
WHERE (((tblToWholesalePreApnd.InvNo) In (SELECT [InvNo] FROM
[tblToWholesalePreApnd] As Tmp GROUP BY [InvNo] HAVING Count(*)>1 )) AND (
(Left([Address],3))<>Left([ShipAddress1],3)));

I have tried many variations, but the above is the only sql that seems to
qualify the duplicate records I would like deleted.

Thanks,

Bill

 

[John Spencer MVP]

Sometimes using the DISTINCTROW predicate will make this work

DELETE DISTINCTROW tblToWholesalePreApnd.*
FROM qselMatchAcctInvByAddressTrimPre INNER JOIN tblToWholesalePreApnd ON
qselMatchAcctInvByAddressTrimPre.InvNo = tblToWholesalePreApnd.InvNo
WHERE (((tblToWholesalePreApnd.InvNo) In (SELECT [InvNo] FROM
[tblToWholesalePreApnd] As Tmp GROUP BY [InvNo] HAVING Count(*)>1 )) AND (
(Left([Address],3))<>Left([ShipAddress1],3)));

Usually you need to write the query so that only the table you need to delete
from is in the FROM clause and you identify which records using a where
clause. I THINK that you would need something like the following.
DELETE
FROM TblWholesalePrApnd
WHERE tblToWholesalePreApnd.InvNo in
(SELECT InvNo
FROM qselMatchAcctInvByAddressTrimPre)
AND InvNo IN
(SELECT [InvNo] FROM
[tblToWholesalePreApnd] As Tmp
GROUP BY [InvNo] HAVING Count(*)>1 ))
AND ((Left([Address],3))<>Left([ShipAddress1],3))))

You should also be able to do something like this. I turned your original
DELETE query into a select query, returned just the InvNO and moved the query
to a subquery in the where clause.

DELETE
FROM tblToWholesalePreApnd
WHERE InvNO in
( SELECT tblToWholesalePreApnd.InvNo
FROM qselMatchAcctInvByAddressTrimPre INNER JOIN tblToWholesalePreApnd ON
qselMatchAcctInvByAddressTrimPre.InvNo = tblToWholesalePreApnd.InvNo
WHERE (((tblToWholesalePreApnd.InvNo) In (SELECT [InvNo] FROM
[tblToWholesalePreApnd] As Tmp GROUP BY [InvNo] HAVING Count(*)>1 )) AND (
(Left([Address],3))<>Left([ShipAddress1],3))))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[BFish via AccessMonster.com]

Thank you so much John. I have been a fan of your work for a few years now.

Bill

Sometimes using the DISTINCTROW predicate will make this work

DELETE DISTINCTROW tblToWholesalePreApnd.*
FROM qselMatchAcctInvByAddressTrimPre INNER JOIN tblToWholesalePreApnd ON
qselMatchAcctInvByAddressTrimPre.InvNo = tblToWholesalePreApnd.InvNo
WHERE (((tblToWholesalePreApnd.InvNo) In (SELECT [InvNo] FROM