Delete Query Could not delete from specified tables

[fred]

I'm trying to run a delete query.

I'm running the following SQL code:

DELETE tblPeople.*, tblPeople.DateCreated, tblCases.CaseID
FROM tblPeople LEFT JOIN tblCases ON tblPeople.PersonID =
tblCases.PersonID
WHERE (((tblPeople.DateCreated)<#6/1/2004#) AND ((tblCases.CaseID) Is
Null));

Access returns the error "Could not delete from specified tables"

tblpeople has a relationship with tblcases.

 

[Douglas J. Steele]

You can't delete from two different tables in the same query.

Try removing

, tblPeople.DateCreated, tblCases.CaseID

from the query.

 

[Ken Sheridan]

I suspect you'll need to use a subquery for this:

DELETE *
FROM tblPeople
WHERE DateCreated < #6/1/2004#
AND NOT EXISTS
(SELECT *
FROM tblCases
WHERE tblCases.PersonID = tblPeople.PersonID);