Delete rows based on value of cell

Posted on 2011-10-08
Medium Priority
Last Modified: 2012-06-21
Hi Experts

Not sure how to explain what I'm trying to get here.  But, in the attached databased. I have the original table with rows that contain the text (No Itemized, etc.) in the Comments Column.  Now I've appended the table and now have rows that contain text (Capacity) in the Comments Column.  I need to delete the row that has the text (No Itemized, etc), and keep the row that has the text (Capacity).

See attached for clarification.

Original Table
Original Results
Question by:mato01
  • 2
  • 2
LVL 77

Expert Comment

ID: 36936145
Your explanation is not sufficient.

You have also kept records that have 'Timing' as well as 'Capacity' and you have deleted records that say 'No Items' as well as ones that have Not itemized this week*"

You need to explain the rules in full.

Based on what you have posted it seems simpler to look at the Constraint column or maybe the Duration column and delete any row that has no value in that column?


Author Comment

ID: 36936646
Thats what I need to delete rows that have

'No Items' as well as ones that have Not itemized this week*

LVL 61

Expert Comment

ID: 36936702
If you are simply trying to delete rows where the text is "No Items" or "Not Itemized" and keep anything else,  this will give you the reults in your results table:

FROM [tbl_Output - Original]
WHERE [Comments] = "No Items" OR [Comments] LIKE "Not Item*"

Open in new window


dim strSQL as string
strSQL = "DELETE * " _ 
            & "FROM [tbl_Output - Original] " _
            & "WHERE [Comments] = 'No Items' OR [Comments] LIKE 'Not Item*'"
Currentdb.execute strSQL, dbfailon error

Open in new window

Also, it looks like there are other criteria that may be more fitting, especially if there are other hand-entered variations on the comments field.  For example, [constraint number] and other fields are NULL in records you seem to want deleted.  If this is a consistent pattern, that might be a better option for your criteria.

Author Comment

ID: 36936874
You're right.  And, I haven't been quite clear.  Even though I know in my mind what I want to do.  Your VBA works fine, but I only want to delete 'No Items' as well as ones that have Not itemized this week* if there is another instance.

See example, for AllGrp (Jeff).  I want to leave this one because there is no other records reference to it in the table.

See example, for AllGrp (Carm).  I want to delete the reference to "Not Itemized this week" because there is another reference in the table that is itemized.

Still not sure If I'm clear. Either way thanks for your help.

LVL 61

Accepted Solution

mbizup earned 1000 total points
ID: 36936917
Try this...

FROM [tbl_Output - Original]
WHERE ([Comments] = "No Items" OR [Comments] LIKE "Not Item*") AND DCount("*","[tbl_Output - Original]", "[AllGrp] ='" & [AllGrp] & "'") > 1

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

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.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

749 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