删除Access中其他表的记录

Question

我有两个表Transactions和TableMaxDateWithZeroRunningTotal． 这些表通过名为小学生的字段连接。出现在两个表中。两个表都有一个名为Date的字段

我想从表中删除Transactions日期<= [TableMaxDateWithZeroRunningTotal].[日期]。我做了以下查询:

DELETE Transactions.* 
FROM Transactions INNER JOIN TableMaxDateWithZeroRunningTotal ON Transactions.[Pupil Ref] = TableMaxDateWithZeroRunningTotal.[Pupil Ref]
WHERE (((Transactions.Date)<=[TableMaxDateWithZeroRunningTotal].[MaxOfDate]));

但是我得到错误"无法从指定的表中删除"。为什么是这样，我该如何解决这个问题?

Answer 1

我在这里找到了解决方案https://answers.microsoft.com/en-us/msoffice/forum/all/use-a-criteria-from-one-table-to-delete-from/e74471ac-f124-473f-b498-b5e646a34935

下面是我的代码

DELETE * 
FROM 
Transactions 
WHERE 
EXISTS (
SELECT 
* 
FROM 
TableMaxDateWithZeroRunningTotal 
WHERE 
Transactions.[Date] <= TableMaxDateWithZeroRunningTotal.MaxOfDate 
and Transactions.[Pupil Ref] = TableMaxDateWithZeroRunningTotal.[Pupil Ref]
);