Delete rows based on value of cell

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
test11.accdb
mato01Asked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
Try this...

DELETE  *
FROM [tbl_Output - Original]
WHERE ([Comments] = "No Items" OR [Comments] LIKE "Not Item*") AND DCount("*","[tbl_Output - Original]", "[AllGrp] ='" & [AllGrp] & "'") > 1
0
 
peter57rCommented:
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?


0
 
mato01Author Commented:
Thats what I need to delete rows that have

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

0
 
mbizupCommented:
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:

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

Open in new window


In VBA:

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.
0
 
mato01Author Commented:
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.



test111.accdb
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.