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

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.
davidpelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Neil RussellTechnical Development LeadCommented:
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.
0
Neil RussellTechnical Development LeadCommented:
Sorry, thats

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

0
Neil RussellTechnical Development LeadCommented:
OR in its simplist form if it is suitable...

SELECT PatientID
FROM Patients
WHERE drugfield  
NOT LIKE "%tylenol%"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

peter57rCommented:
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 ?
0
Neil RussellTechnical Development LeadCommented:
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*"
)

0
Dale FyeCommented:
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.
0
davidpelAuthor 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.
0
peter57rCommented:
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.
0
davidpelAuthor 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.  

0
Dale FyeCommented:
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


0
davidpelAuthor 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"));
0
Dale FyeCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
davidpelAuthor Commented:
excellent follow-through!
0
Dale FyeCommented:
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*")
0
Neil RussellTechnical Development LeadCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.