Delete Query Problem

[Jeff C]

I am not able to get around the "Specify Table you want to delete records
from.." warning on the following:

SELECT ReportData.Acct
FROM ReportData LEFT JOIN DailyReport ON ReportData.Acct = DailyReport.Num
WHERE (((ReportData.DR) Is Null) AND ((DailyReport.Num) Is Null));

I am stumped. Any ideas?

Thanks Very Much.

 

[Allen Browne]

A subquery should do it.

Not sure if this is exactly what you intend, so backup first and treat it as
an example:

DELETE FROM ReportData
WHERE ReportData.DR Is Null
AND NOT EXISTS
(SELECT DailyReport.Num
FROM DailyReport
WHERE DailyReport.Num = ReportData.Acct);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[Jeff C]

A subquery should do it.

I thought it would take one but didn't get it structures correctly.

Not sure if this is exactly what you intend, so backup first and treat it as
an example:

DELETE FROM ReportData
WHERE ReportData.DR Is Null
AND NOT EXISTS
(SELECT DailyReport.Num
FROM DailyReport
WHERE DailyReport.Num = ReportData.Acct);

Worked perfectly!!

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

I am adding this to the other references from your website I have pinned to
my bulletin board. It's been awhile.

Thanks very much again.