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"))

Any ideas?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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.

Kevin CrossChief Technology OfficerCommented:
Try something like this:
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")

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Andy419Author Commented:
That did it -- THANKS!!!
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.


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"
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 ---  :-)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.