Solved

Access SQL Query to Delete records based on Condition

Posted on 2015-02-24
5
170 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
  • 3
  • 2
5 Comments
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 500 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dsum Function for List Box Data 7 45
Access 2016 - Labels prompt to print 4 31
Help with DoEvents 8 26
Progress bar in access form 10 13
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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 …

773 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