Delete Query Problem

J

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.
 
A

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
 
J

Jeff C

Allen Browne said:
A subquery should do it.
Click to expand...
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);
Click to expand...

Worked perfectly!!
If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html
Click to expand...
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.
 
Top