Select Query (Probably easy but I can't figure it out)

[Guest]

This has GOT to be easy, but I can't figure out how to do it: I have a DB
whose tables have unique Account Numbers (one with patient demographic info,
a second with lab values, and a third which contains records of those
patients NOT on medication; there are some others but my question has to do
with these).

Each patient has had multiple blood tests done over time on different
dates. Let's say that one of those tests is cholesterol (i.e., each patient
has had a number of cholesterol tests done over time). So, in my Lab table,
each Account Number (patient) will have a single record for each cholesterol
drawn and the date it was drawn.

I want to find only those patients whose cholesterol has always been under
200 and who are NOT on medication.

I have tried each of the three joins between the tables (Lab and NotOnMeds).
I put the Account Number from the NotOnMeds table in the first query column
and in the Criteria row of the column for cholesterol value (which is a
number field in the table) I put <200. What I get, of course, are all of the
records from the Lab table where cholesterol is <200. I want only those
Account Numbers (patients) who are Not on Meds and who have never had a value

=200, not just the instances where an Account Number has <200.

Thanks for the help.

 

[Douglas J. Steele]

Create a query of all patients who have ever had their cholesterol over 200,
using SELECT DISTINCT PatientID FROM Lab WHERE Cholesterol >= 200. Now,
create a query that returns all patients who are not on meds who are also
not in the list of patients above.