Essentially I would like the MS Access query to output all records that meet all three of the following criteria:

1. Expanded_Status = "Eligible but not enrolled"
2. Hospital = "UHN"
3. Comments = "transplant" or "tx" or "post transplant"

The comments field has 3 different versions of the word "transplant" so some fields may have "tx" instead of "transplant" and some records may have "post transplant" instead of "transplant".

The query below is outputting records that don't match with all three of the criteria for some reason.

Please help me modify the query so that I can find only records with all three criteria fulfilled

SELECT [First Name], [Last Name], [Subject ID], Expanded_Status, Hospital, Comments
FROM [Barriers UHN Screen - 2017 Mailing]
WHERE Expanded_Status = "Eligible but not enrolled" AND Hospital = "UHN"  AND Comments LIKE "transplant"  OR Comments OR "post transplant" OR Comments = "tx";

CodePudding user response：

Building on @Harun24HR 's answer:

SELECT 
    [First Name], [Last Name], [Subject ID], Expanded_Status, Hospital, Comments
FROM 
    [Barriers UHN Screen - 2017 Mailing]
WHERE 
    Expanded_Status = "Eligible but not enrolled" AND Hospital = "UHN"  AND 
(LOWER(Comments) LIKE "*transplant*"  OR LOWER(Comments) LIKE "*post transplant*" OR LOWER(Comments) LIKE "*tx*");

I added two things:

1. Calling lower on Comments makes sure you are only comparing lower cased string - if someone has "Post Transplant" in the comments, it might not match on "post transplant"
2. Wildcards * on the beginning and end of the comparison string - this tells Access my comparison string can be anywhere in the comments.

Like Operator

CodePudding user response：

Zuva, Try this

SELECT 
    [First Name], [Last Name], [Subject ID], Expanded_Status, Hospital, Comments
FROM 
    [Barriers UHN Screen - 2017 Mailing]
WHERE 
    Expanded_Status = "Eligible but not enrolled" AND Hospital = "UHN"  AND 
(Comments = "transplant"  OR Comments = "post transplant" OR Comments = "tx");

Enclosing the Comment section of the where clause in brackets, tells access to evaluate each separately as part of the AND clause.