Delete Queries

  • Thread starter Thread starter mcl
  • Start date Start date
M

mcl

I have been able to get a delete query to display the records I want to
delete when I select view but when I try to execute it, it says it's can't
delete the records. What would cause that?
Here's the actual sql code:

DELETE Datsav3.*, Audit_by_Year_Month.CountOfmo
FROM Datsav3 INNER JOIN Audit_by_Year_Month ON (Datsav3.blkstn =
Audit_by_Year_Month.blkstn) AND (Datsav3.year = Audit_by_Year_Month.year)
AND (Datsav3.mo = Audit_by_Year_Month.mo)
WHERE (((Audit_by_Year_Month.CountOfmo)<10));
 
The problem is, you are not allowed to delete any records
which are based on two tables (using Join)
You can only update or delete on side of table at a time

May
MCP in Access and SQL Server
 
Try putting only ONE table in the DELETE clause.

DELETE Datsav3.*
FROM Datsav3 INNER JOIN Audit_by_Year_Month ON (Datsav3.blkstn =
Audit_by_Year_Month.blkstn) AND (Datsav3.year = Audit_by_Year_Month.year)
AND (Datsav3.mo = Audit_by_Year_Month.mo)
WHERE (((Audit_by_Year_Month.CountOfmo)<10));

I suspect that this may still fail since it looks as if you may be using an
aggregate query to Audit_by_Year_Month as part of the Delete query.
 
Back
Top