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

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.

Sorry, thats

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

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

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

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 ?

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*"
)

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.

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.

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.  

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

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

excellent follow-through!

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*")

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.