Could not delete from specified tables problem

[Guest]

I have the following select statement for my .mdb applcaiton and I get the
following error "Could not delete from specified tables" Any ideas on what I
am doing wrong?

DELETE GBPEXCFMASTERCAF.*
FROM (GBPEXCFMASTERCAF LEFT JOIN GBPEXCFMASTERFLIFLC ON
GBPEXCFMASTERCAF.ASWDocNum = GBPEXCFMASTERFLIFLC.ASWDocNum) LEFT JOIN
TRANSFER_MEMBER ON GBPEXCFMASTERFLIFLC.StoreNum = TRANSFER_MEMBER.CMCUNO
WHERE (((GBPEXCFMASTERFLIFLC.StoreNum)<>"0266" And
(GBPEXCFMASTERFLIFLC.StoreNum)<>"0673" And
(GBPEXCFMASTERFLIFLC.StoreNum)<>"0306" And
(GBPEXCFMASTERFLIFLC.StoreNum)<>"3060"));

Any help will be greatly appreciated
ToniS

 

[John Spencer]

Try
DELETE DISTINCTROW GBPEXCFMASTERCAF.*
FROM (GBPEXCFMASTERCAF LEFT JOIN GBPEXCFMASTERFLIFLC ON
GBPEXCFMASTERCAF.ASWDocNum = GBPEXCFMASTERFLIFLC.ASWDocNum) LEFT JOIN
TRANSFER_MEMBER ON GBPEXCFMASTERFLIFLC.StoreNum = TRANSFER_MEMBER.CMCUNO
WHERE (((GBPEXCFMASTERFLIFLC.StoreNum)<>"0266" And
(GBPEXCFMASTERFLIFLC.StoreNum)<>"0673" And
(GBPEXCFMASTERFLIFLC.StoreNum)<>"0306" And
(GBPEXCFMASTERFLIFLC.StoreNum)<>"3060"));

If that fails then perhaps the following will delete the records you want
deleted.
DELETE DISTINCTROW GBPEXCFMASTERCAF.*
FROM GBPEXCFMASTERCAF
WHERE ASWDocNUM in
(SELECT ASWDocNum
FROM GBPEXCFMASTERFLIFLC LEFT JOIN TRANSFER_MEMBER
ON GBPEXCFMASTERFLIFLC.StoreNum = TRANSFER_MEMBER.CMCUNO
WHERE StoreNum NOT IN ("0266","0673",0306","3060"))

I'm not sure you even need the Transfer_Member table in these queries since
you are using a left join this would have no impact on the records that are
returned. And the Left JOIN to GBPEXCFMASTERFLIFLC makes little sense
since applying criteria to GBPEXCFMASTERFLIFLC .StoreNum will negate the
LEFT JOIN and give the same results as an INNER JOIN.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..