I want to write a query that will count the number of clients on a certain class of drugs.
Then if they have more than 2 of those drugs on their profile, I want this on output. If they dont have 2 or more, I dont want them.
I would love to do this in one query but I am thinking I have to make 2 queries. One to count the drugs, then another one to ADD other information such as name, date, drug name .....
I have 2 tables I am gathering information from - ORDERS and Clients. The Client number is a unique client identifier.
Criteria needed: End query must show clients who are on 2 or more drugs that start with the Thera_code of 08. The drug must not be stopped (NO in the Stopped column)
I have tried placing the "count" in the query, but I just cant get it to work! I have attached the database for reference.
Here is the SQL without the COUNT:
SELECT CLIENTS.Client_NUM, ORDERS.THER_CODE_1, CLIENTS.NRS_STATION, CLIENTS.BED_ABBREV, CLIENTS.PTNAME, CLIENTS.PAT_NUM, ORDERS.DRUG_CODE, ORDERS.BRAND_NAME, ORDERS.GENERIC_NAME, ORDERS.STOPPED
FROM CLIENTS INNER JOIN ORDERS ON CLIENTS.Client_NUM = ORDERS.Client_NUM
WHERE (((ORDERS.THER_CODE_1) Like "08*") AND ((ORDERS.STOPPED)="NO"));