0

I hope someone can help me since I’m very new to SQL and coding and I can’t wrap my head around the following problem.

I have a table containing

patient name, prescription date, medication (containing multiple drugs separated by spaces).

There is, in most cases, more than one row per name since people get prescriptions from time to time.

What I want to do is generate a list of all patients that ever received Medication A and Medication B.

Logically, I would identify all patients that ever received Medication A and also all patients that ever received Medication B. Then I would select all records that appear in both groups. Then I would like to generate a list of all names that appear in both groups, How do I do this in MS-Access SQL?

Many thanks in advance!

samtheman
  • 1
  • 2
  • you want to select all the patient who have taken both Medication A and B ?? – Praveen DA May 08 '19 at 10:04
  • Is the column `mdication` a comma separated list? – forpas May 08 '19 at 10:14
  • Thanks for your ideas. Yes, I want to select all patients who have taken both medication A and B (consecutively or at the same time). And yes, the column medication is a space (!) separated list – samtheman May 08 '19 at 11:10

2 Answers2

0

You can use group by and having. Assuming there are no duplicates, you can do:

Select patient_name
from table_name
where medication in ("medication A", "medication B")
group by patient_name
having count(*) = 2;

If you have duplicates, then you could use count(distinct) in most databases. But MS Access does not support this. Instead:

select patient_name
from (select distinct patient_name, medication
      from table_name
     ) as pm
where medication in ("medication A", "medication B")
group by patient_name
having count(*) = 2;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I don't know why but this returns an empty table. What exactly is the 'count' counting here? – samtheman May 10 '19 at 08:43
  • @samtheman . . . Presumably, that would occur if the `in` values do not exactly match what is in the data (or if no patients are on both medications). – Gordon Linoff May 10 '19 at 11:44
0

You need 2 queries, each getting the names of the patients who have received each medication and then join them to get their intersection.
You can use the LIKE operator to search for the name of the medication inside the column.

select  a.name 
from  (
  select distinct name from tablename 
  where ' ' & medication & ' ' like '* medicationA *'
) as a inner join (
  select distinct name from tablename 
  where ' ' & medication & ' ' like '* medicationB *'
) as b
on b.name = a.name
forpas
  • 160,666
  • 10
  • 38
  • 76
  • I'm getting the error message "Couldn't add table '(' ". I don't know if this is important, but I'm trying this via "Microsoft Query" from within Excel. – samtheman May 10 '19 at 09:01
  • This code is for MS-ACCESS. I don't know about "Microsoft Query". take a look to this: https://stackoverflow.com/q/31516811/10498828 – forpas May 10 '19 at 11:05