[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Delete Query with Criteria from another table

Posted on 2008-11-07
7
Medium Priority
?
250 Views
Last Modified: 2012-05-05
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?
0
Comment
Question by:Andy419
  • 3
  • 3
7 Comments
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 22904910
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
 

Author Comment

by:Andy419
ID: 22905028
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 22905223
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Closing Comment

by:Andy419
ID: 31514362
That did it -- THANKS!!!
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 22905316
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
 

Author Comment

by:Andy419
ID: 22905429
Thanks again for the help -- that is close -- I now received a "cannot delete from specified table".

Thanks for explaining in "design mode"
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 22905514
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question