?
Solved

Access SQL Query to Delete records based on Condition

Posted on 2015-02-24
5
Medium Priority
?
182 Views
Last Modified: 2015-02-24
I have a table that needs to be dedicated to just one customer. However, the records do not have the customer names listed like I would like. I need to delete some records based on a condition that is one of the fields.

Table Name: DSCNSKPIUPDATE
Field Name: Reject

Condition: R0*

So In my field "Reject" I have several different formats of text. However, I only want to keep the records that start with "R0".

** The records have back up copies for the other customers **

The text look like these...
R0117730
R0116926
R0118216
or
N/A
20140903436
2014070323
1
To be released

As you can see there are many different formats. I need to remove all records that do not start with "R0".
0
Comment
Question by:ggodwin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 2000 total points
ID: 40628129
You can use this query..

I'm assuming you are using this in access:-

Delete * from  DSCNSKPIUPDATE
where left(Reject,2)<>"RO" 

Open in new window


Change the RO one in single quotes if you are working in SQL and remove * from the same...

Saurabh...
0
 

Author Closing Comment

by:ggodwin
ID: 40628143
Worked perfectly
0
 

Author Comment

by:ggodwin
ID: 40628150
If the field "Reject" is NULL can I use this?

Delete * from DSCNSKPIUPDATE
Where Reject is NULL

Open in new window

0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 40628184
Yeah that should be taken care of as the left,2 is not RO but you can use this...

Delete * from  DSCNSKPIUPDATE
where left(Reject,2)<>"RO"  or reject is null

Open in new window


This will do that...

Saurabh...
0
 

Author Comment

by:ggodwin
ID: 40628215
Thank you. For what ever reason it did not get covered in the Left,2
0

Featured Post

Give Your Engineering Team a Productivity Boost

Learn why container technology is so powerful and how it can provide your team with productivity gains and other benefits.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

771 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