Delete Query with Criteria from another table

Hi Everyone,

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;


Any ideas?
Andy419Asked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Try something like this:
DELETE 
FROM Scans
WHERE ((Scans.Delete)=Yes) AND EXISTS (SELECT 1 FROM [Skid Manifest Data] WHERE Scans.UniqueID = [Skid Manifest Data].UniqueID AND [Skid Manifest Data].SkidPaid="no")
WITH OWNERACCESS OPTION;

Open in new window

0
 
Eric ShermanAccountant/DeveloperCommented:
Since you are using Query Design View ....

Add both tables to the Query then Join them based on the field that exist in both tables.  The PUID  I'm guessing here.

Then set your criteria based on the appropriate field from the table(s).

ET
0
 
Andy419Author Commented:
I have all that.  I think my issue is that I need to put the "Paid" field form teh "Skid Manifest Data" table into the query design so that I can filter out anything marked "yes" (paid).  I think that since any entry from the data table is in the design, Access doesn't know what table it needs to delete from.

I've added a screen shot of the design view.


Delete-Query.doc
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Andy419Author Commented:
That did it -- THANKS!!!
0
 
Eric ShermanAccountant/DeveloperCommented:
Ok, this should be easy enough to correct.  As the error message explains ... Your query only defines the Criteria to Delete.  You could do following ...

1.) Double Click the * in your Scans table and set the Delete line in the query grid to From.

Then check the SQL of your query and compare it to what you posted above.

ET

0
 
Andy419Author Commented:
Thanks again for the help -- that is close -- I now received a "cannot delete from specified table".

Thanks for explaining in "design mode"
0
 
Eric ShermanAccountant/DeveloperCommented:
Ok, glad you got it fixed.   I had stepped away from my computer and did not realize the question had already been closed.

Got to hit the Refresh ---  :-)

ET
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.