Access Database - SQL, Filtering by Multiple Items in Same Column

Question

I'm trying to create a SQL query in Access that will return all fields (SELECT *) when KEY is associated with three over the counter drugs (Motrin, Tylenol, and Bayer). In the example below, all rows for AMYZ32874 would be returned and none of the rows for WillyV32906 would be returned because he's only associated with 2 of the 3 drugs.

Starting Data:

Key         Name      DOB       OTC
AmyZ32874   Amy Z     1/1/1990  Motrin
AmyZ32874   Amy Z     1/1/1990  Tylenol
AmyZ32874   Amy Z     1/1/1990  Bayer
WillyV32906 Willy V   2/2/1990  Motrin
WillyV32906 Willy V   2/2/1990  Tylenol
WillyV32906 Willy V   2/2/1990  Tylenol

Desired Result:

Key         Name      DOB       OTC
AmyZ32874   Amy Z     1/1/1990  Motrin
AmyZ32874   Amy Z     1/1/1990  Tylenol
AmyZ32874   Amy Z     1/1/1990  Bayer

I'm pretty stuck.... This is my latest attempt.

SELECT *
FROM SAMPLEDB
WHERE OTC IN ('Tylenol', 'Motrin', 'Bayer') and Key in
(SELECT Key FROM SAMPLEDB
GROUP BY Key HAVING COUNT (Key) > 2);

Side note: avoid using reserved words (like `Key`) in your tables whenever possible. They can make more complex SQL statements confusing, and sometimes will give you unexpected results. If you can't avoid the name (and sometimes you can't), try enclosing it in brackets to help reduce confusion. — C. White, May 15 '16 at 23:06

score 1 · Accepted Answer · edited May 16 '16 at 01:14

1

It's a little ugly to look at, but I believe this should work:

SELECT 
  *
FROM 
  SAMPLEDB
WHERE 
  OTC IN ('Tylenol', 'Motrin', 'Bayer') 
  AND [Key] in
    (
      SELECT 
        [Key] 
      FROM 
        (
          SELECT 
            [Key]
            , OTC 
          FROM 
            SAMPLEDB
          WHERE 
            OTC IN ('Tylenol', 'Motrin', 'Bayer')
          GROUP BY 
            [Key]
            ,OTC 
        ) AS S1
      GROUP BY  
        [Key]
      HAVING 
        COUNT ([Key]) > 2
    );

edited May 16 '16 at 01:14

Ollie

337
3
6
17

answered May 15 '16 at 23:02

C. White

802
1
7
19

Thank you, but I'm getting an error message. It says "Syntax Error (missing operator) in Query Expression 'OTC WHERE OTC IN ('Tylenol', 'Motrin', 'Bayer') "... I'm not sure what to think. It looks good to me. – Ollie May 15 '16 at 23:54
1

I figured it out. The WHERE clause needs to go before the GROUP BY. I think I can change it, and you'll need to review it mostly likely. I'll mark it as solved. Thanks!!! – Ollie May 16 '16 at 00:11
Good catch. The perils of writing SQL with a concussion :) Thanks for the correction! – C. White May 16 '16 at 09:32

score 0 · Answer 2 · answered May 15 '16 at 22:09

0

You should put the same "WHERE" statement in your sub-query:

(SELECT Key FROM SAMPLEDB
WHERE OTC IN ('Tylenol', 'Mothrin', 'Bayer')
GROUP BY Key 
HAVING COUNT(Key) > 2)

answered May 15 '16 at 22:09

cChacon

184
1
8

Hi @cChacon, thank you, but this didn't work out. I'm still getting the same result. – Ollie May 15 '16 at 22:48

Access Database - SQL, Filtering by Multiple Items in Same Column

2 Answers2