CODEWITHSUNDEEP
sqlms-accessjet-sql
Ollie
Ollie

Reputation: 337

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

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);

Upvotes: 2

Views: 385

Answers (2)

C. White
C. White

Reputation: 800

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
    );

Upvotes: 1

cChacon
cChacon

Reputation: 184

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)

Upvotes: 0

Related Questions