Delete Query not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I got the following message after issuing a delete query:

Could not delete from specified tables

The code is as follows:
DELETE tblPCSR.*, tblPCSR.StandType, tblEmployees.HRNum
FROM tblPCSR LEFT JOIN tblEmployees ON tblPCSR.HRNum = tblEmployees.HRNum
WHERE (((tblPCSR.StandType)<>"C") AND ((tblEmployees.HRNum) Is Null));

Why would the data not be able to be deleted ? When I ran as a select, it
listed a record.
 
Which data do you want to delete? Just the data in the tblPCSR table? If
yes, try this:

DELETE tblPCSR.*
FROM tblPCSR LEFT JOIN tblEmployees ON tblPCSR.HRNum = tblEmployees.HRNum
WHERE (((tblPCSR.StandType)<>"C") AND ((tblEmployees.HRNum) Is Null));

or try this:

DELETE tblPCSR.*
FROM tblPCSR
WHERE tblPCSR.StandType<>"C" AND
tblPCSR.HRNum IN
(SELECT tblEmployees.HRNum
FROM tblEmployees
WHERE tblEmployees.HRNum Is Null);
 
I am trying to delete from table tblPCSR. When I tried the first code listed
below, I got the same error: Could not delete from specified tables.

When I tried the second example below, it did not link the to the employee
table, so could not find the records to delete. The idea is to link to the
employee table with an outer join. Any record on table tblPCSR which is not
on table tblEmployees
should be deleted.

Is there anything on the table itself that could be preventing deletion. Or
from the message: Could not delete from specified tables. Does this mean it
thinks its trying to delete from more than 1 table ?
 
Sorry - I wasn't understanding what the query was trying to do. Perhaps this
will be better:

DELETE tblPCSR.*
FROM tblPCSR
WHERE tblPCSR.StandType<>"C" AND
tblPCSR.HRNum NOT IN
(SELECT tblEmployees.HRNum
FROM tblEmployees);
 
Worked like a charm. Thanks for the help.

Ken Snell (MVP) said:
Sorry - I wasn't understanding what the query was trying to do. Perhaps this
will be better:

DELETE tblPCSR.*
FROM tblPCSR
WHERE tblPCSR.StandType<>"C" AND
tblPCSR.HRNum NOT IN
(SELECT tblEmployees.HRNum
FROM tblEmployees);
Click to expand...
 
Back
Top