Delete Query with Criteria from another table
Posted on 2008-11-07
I'm trying to delete records in one table with one criterion based in another table. In words: "delete record from "Scans" table if it is marked for delete and also it is not marked as paid in the "Skid Manifest Data" table. I am getting an error that says "specify the table containing the records that you want to delete". I have seen some threads on this, but I do not know enough about SQL to read through them correctly. I am trying to do this in design view but I have attached the SQL code below.
DELETE Scans.PUID, Scans.ScanData, Scans.ScanTime, Scans.UniqueID, Scans.JobNumber, Scans.Delete
FROM Scans LEFT JOIN [Skid Manifest Data] ON Scans.UniqueID = [Skid Manifest Data].UniqueID
WHERE (((Scans.Delete)=Yes) AND (([Skid Manifest Data].SkidPaid)="no"))
WITH OWNERACCESS OPTION;