?
Solved

Correct Access SQL Statement for Multiple Criteria Deletion

Posted on 2009-02-14
7
Medium Priority
?
454 Views
Last Modified: 2013-11-07
I would like to delete rows from an Access table, I will be creating criteria with SQL DELETE and WHERE statements, for two separate criteria, in two fields. I have attempted using a DELETE query as well,

For Example:
DELETE FROM table WHERE Field1="Instances" AND Field2 = "Findings";
 
Field1 criteria may be in rows 1 - 4, and Field2 criteria in rows 7 - 10. Assume that there are no rows in common that fulfill both criteria. In one SQL programming statement, I want to delete all of these 8 rows, and have not been able to thus far.

Thanks
0
Comment
Question by:tahirih
  • 2
  • 2
  • 2
6 Comments
 
LVL 25

Accepted Solution

by:
reb73 earned 1000 total points
ID: 23640580
Change the AND to OR as follows -

DELETE FROM table WHERE Field1="Instances" OR Field2 = "Findings";



0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1000 total points
ID: 23640598
If this is run within Access, Jet may prefer this subtly different syntax:

DELETE * FROM table WHERE Field1="Instances" OR Field2 = "Findings";

No points for this, please :)
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 23641996
tahirih,

I appreciate the points, but in fairness you should have given reb73 at least a split.

Regards,

Patrick
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:tahirih
ID: 23667268
Please confirm howI can split the points. Thanks
0
 

Author Comment

by:tahirih
ID: 23672481
I wanted to split the points. Thanks
0
 
LVL 25

Expert Comment

by:reb73
ID: 23672656
Thanks tahirih..  Patrick, you're a gentleman.. Cheers!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

862 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