I am trying to create a query that selects only a set of values, but excludes other ones.
The column I am selecting it's called "Participants" and the values therein are not a single text item, but a list of items separated by commas (length and number of items vary), for example: "Alpha, Delta, Eta" or the value: "Beta, Gamma or the value: "Delta, Beta, Epsilon, Theta, Vau".
I wrote this query to try and obtain results that will only include value like Delta, Eta, Epsilon and Vau but not Alpha, Beta, Gamma (it means that I don't want the value: "Alpha, Delta, Eta" to be included, but I want values like: "Delta, Zeta, Epsilon")
SELECT DISTINCT Name, Category, CODE, Participants WHERE(Participants NOT LIKE '%Alpha%' OR Participants NOT LIKE '%Beta%' OR Participants NOT LIKE '%Gamma%') AND (Participants LIKE '%Delta%' OR Participants LIKE '%Eta%' OR Participants LIKE '%Epsilon%' OR Participants LIKE '%Vau%');
The results include all the values specified in the LIKE part of the query, but NOT LIKE doesn't seem to work as I keep getting results like: "Alpha, Eta, Epsilon.
I could create multiple queries in Access to filter with the LIKE query and then with the NOT LIKE, but I would like to find a way to do it in a single query.
Online I could only find examples where there is a single LIKE and a NOT LIKE in the query
Can anybody suggest a solution please?