Delete rows based on value of cell

Posted on 2011-10-08
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
    LVL 77

    Expert Comment

    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

    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

    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.

    Author Comment

    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

    Try this...

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

    759 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

    13 Experts available now in Live!

    Get 1:1 Help Now