Solved

Access SQL Query to Delete records based on Condition

Posted on 2015-02-24
5
169 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Peformance + mulitple query plans 9 48
DBF to ... Converter 5 43
SQL Query Syntax Join 4 34
format date field on certain entries 8 29
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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 …

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now