How do I identify a list of users that don't contain specific data?

davidpel
davidpel used Ask the Experts™
on
i need to put in criteria in an MSAccess 2007 query that will result in giving me a list of users that DO NOT contain the specified drug(s) entered into one of the fields.  if i have a drug such as tylenol, i can tell the query to find me all patients that have this drug, but now i want to find all patients that DO NOT have this drug on their profile; basically the exact opposite of the first query.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Neil RussellTechnical Development Lead

Commented:
You could use a NOT IN query like this

first define a query that lists all the patients ID's that DO have the drug, i.e.

SELECT PatientID
FROM Patients
WHERE drugfield  
LIKE "%tylenol%"

then you can simply use

SELECT PatientID
WHERE PatientID
NOT IN
(
SELECT PatientID
FROM Patients
WHERE drugfield  
LIKE "%tylenol%"
)

Adjust to suit your tables & fileds.
Neil RussellTechnical Development Lead

Commented:
Sorry, thats

SELECT PatientID
FROM Patients
WHERE PatientID
NOT IN
(
SELECT PatientID
FROM Patients
WHERE drugfield  
LIKE "%tylenol%"
)

Neil RussellTechnical Development Lead

Commented:
OR in its simplist form if it is suitable...

SELECT PatientID
FROM Patients
WHERE drugfield  
NOT LIKE "%tylenol%"
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Access uses * not % for native queries.

But I am more concerned about this comment..
"....entered into one of the fields..."

Are you saying that you have multiple 'drug' fields in the same record ?
Neil RussellTechnical Development Lead

Commented:
Sorry yes * not %, was in sequel mode :P

Regardless of how many drug fields you have, the principal is exactly the same. In your sub query, the bit inside the ( ), you just include all the fields your interested in.

i.e.

SELECT PatientID
FROM Patients
WHERE PatientID
NOT IN
(
SELECT PatientID
FROM Patients
WHERE drugfield  
LIKE "*tylenol*"
OR
drugfield2
LIKE "*Morphine*"
OR
drugfield3
LIKE "*salbutamol*"
)

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Agree with Peter's concerns about having more than one drug listed in a text field of a patients record.

You should have a separate "Patient_Drugs" table which contains PatientID and Drug (or DrugID), or maybe even a Patient_Drug_History table, which contains the PatientID, DrugID, dosage, quantity, and start/end dates.

Author

Commented:
I'm sorry, i'm a bit confused.  what i need is a list of patients that do NOT have the dummy drug order "assessment of vaccine" on their order profile at all.  if i use the "not like" function such as not like (assess*), it simply shows me all patients but strips out the "assessment of vaccine" order so i can't see it, but all the patients still have the assessment order on their profiles, it has just been excluded from the result.
The solution posted by Neilsr (apart from the * for %) should give you exactly that.

If you getting something else then we need to see your version of the query.

Author

Commented:
i must not be writing it correctly.  i don't understand the NOT IN portion as it is written above.  My two tables are:  BI and PHM_ORDERS.  from them, i extract out the following fields into a local table:

pat_num, ptname, nrs_station, bed_abbrev, site, and drug_name

and then put LIKE "ASSESS*" in the drug_name field and it gives me all the patients that have a drug with the name of assessment vaccine.  

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
David,

What is the entire SQL statement of the query that gives you those the do contain the "ASSESS*" drug?  In order for us to give you a meaningful answer, we must understand your table structure.

Dale


Author

Commented:
SELECT BI.PAT_NUM, BI.PTNAME, BI.SITE, BI.NRS_STATION, BI.BED_ABBREV INTO assess_lcl
FROM BI INNER JOIN PHM_ORDERS ON BI.ITN = PHM_ORDERS.ITN
WHERE (((PHM_ORDERS.PRIMARY_NAME) Like "assess*"))
GROUP BY BI.PAT_NUM, BI.PTNAME, BI.SITE, BI.NRS_STATION, BI.BED_ABBREV
HAVING (((BI.SITE)="3"));
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
So, if I understand correctly, you only want those records from BI, where "Assess*" is not found in any of the records associated with the ITN value of the BI table.  If that is correct, then you should use:

SELECT DISTINCT BI.PAT_NUM, BI.PTNAME, BI.SITE, BI.NRS_STATION, BI.BED_ABBREV INTO assess_lcl
FROM BI INNER JOIN PHM_ORDERS ON PHM_ORDERS.ITN = PHM_ORDERS.ITN
WHERE BI.SITE="3"
AND BI.ITN NOT IN (SELECT DISTINCT ITN FROM PHM_ORDERS
                               WHERE PHM_ORDERS.PRIMARY_NAME Like "assess*")

You don't need the GROUP BY or HAVING clause in your query since you are not doing an aggregation.

The SELECT statement in the paranthesis following "Not IN" will identify all of the ITN values which contain a record which contains "Assess" at the beginning of the [Name] field.  The NOT IN causes Access to filter those records OUT of your result set.

Author

Commented:
excellent follow-through!
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Actually, an even simpler version would have been:

SELECT BI.PAT_NUM, BI.PTNAME, BI.SITE, BI.NRS_STATION, BI.BED_ABBREV INTO assess_lcl
FROM BI
WHERE BI.SITE="3"
AND BI.ITN NOT IN (SELECT DISTINCT ITN FROM PHM_ORDERS
                               WHERE PHM_ORDERS.PRIMARY_NAME Like "assess*")
Neil RussellTechnical Development Lead

Commented:
Next time would help when asking about SQL queries IF you stated all the facts. Like joined multiple tables, what your current query is etc.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial