Delete query error 3086

[Jo]

Database for school applications. Father table linked to Mother table linked
to Student table. I want to delete where the student ID has no father -
which can happen if data entered incorrectly. I have designed a series of
select queries to identify the records to be deleted where the studentID has
not mother, then the motherID has no father. I have linked this query to
each related table on either StudentID or MotherID to delete the records in
related tables. This has worked on some tables but not others. The delete
queries find the records but cannot delete them. Message says Could not
delete from specified tables. Error message 3086. But it is NOTa read-only
issue. Any suggestions?

 

[Allen Browne]

Switch the problem query to SQL View (View menu in query design.)

Post the SQL statement in a rely to this thread.

 

[Jo]

DELETE tblMother.*
FROM qry_cleanup2 INNER JOIN tblMother ON qry_cleanup2.MotherID =
tblMother.MotherID;

 

[Allen Browne]

Try:
DELETE FROM tblMother
WHERE EXISTS
(SELECT MotherID FROM qry_cleanup2
WHERE qry_Cleanup2.MotherID = tblMother.MotherID);

If subqueries are a new idea, see:
Subquery basics
at:
http://allenbrowne.com/subquery-01.html

 

[Gary Walter]

In addition to Allen's sage help,
DELETE queries based on more
than one table require DISTINCTROW...

DELETE DISTINCTROW tblMother.*
FROM qry_cleanup2 INNER JOIN tblMother