CODEWITHSUNDEEP
sqlms-accessvbams-access-2013
Akkitech
Akkitech

Reputation: 39

Access Query to Delete data based on Complex criteria

I'm using Access 2013. I've a Table name - Raw and its Fields are:

requestid   - Number
Formname    - Short Text
Timestamp   - Date/Time
Type        - Short Text
ActionBy    - Short Text

Raw table contains all the data. Same request ID will be having different Types and actionby. I want to delete all the rows for those request ID where any of the actionby for a particular request ID is not matching my user. If matching, leave all the rows.

Example - In the screenshot, request ID 27176 is having Ciaran in actionby. Hence, it should not delete any of the rows for request ID 27176.

However for request ID 27434, ciaran is not in Actionby so delete all the rows for 27176. Hope I'm able to give a glimpse of what I needed. Raw Table Data

Upvotes: 0

Views: 56

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You can identify the one to delete using NOT IN and some string manipulation:

delete from raw as r
    where requestid not in (select requestid
                            from raw as r2
                            where r2.ActionBy = "Harford, Claran" and
                                  r2.requestid = r.requestid
                           );

Upvotes: 1

Related Questions