Criteria

[Liverlass3981]

Hi,

I have an Access database and I'm creating a query in design view. I'd like
to bring up a list of all patients who take only aspirin - no other
medication at all. So I've dragged down the Patient ID No, then the
medication No. Aspirin is number 5. So I've put '5' in the criteria space and
when run, it brings up all the patients who take aspirin.
But what would I put in the criteria space to bring up those who take the
aspirin and nothing else?
Many thanks.

 

[KARL DEWEY]

The easy way is to use two queries.
First one like this --
qryAllMedNotAsprin --
FIELDS ID Medication
TABLE YourTable YourTable
CRITERIA <>5

Second query place YourTable and qryAllMedNotAsprin in space above design
view grid. Click on ID of YourTable and drag to ID of qryAllMedNotAsprin.
Double click on the connecting line and select option to show all records
from YourTable and only those from qryAllMedNotAsprin that match.
Drag down the required fields of YourTable and ID of qryAllMedNotAsprin.
Under ID of qryAllMedNotAsprin in the criteria row of the grid type 'Is
Null' without quotes.

 

[Jerry Whittle]

You first need to create a query to exclude those who take other drugs. You
need the Patient ID No and the medication No. I n the criteria put <> 5 under
the medication number field. Run it and make sure that no 5's show up or
anything like Null or blanks. If it works, open up this query in SQL view and
copy the SQL statement. Maybe paste it into a Word document for holding.

Then open up your current query in SQL view. Make the Where clause something
like the following:

WHERE [Patient ID No] Not In ( SQL statement from above here )

Make sure that there is only one semi-colon ; it the SQL statement. It
should be at the end.

 

[Liverlass3981]

Many thanks Jerry,
I'll see if I can get a good result with your instructions.
Best regards.

You first need to create a query to exclude those who take other drugs. You
need the Patient ID No and the medication No. I n the criteria put <> 5 under
the medication number field. Run it and make sure that no 5's show up or
anything like Null or blanks. If it works, open up this query in SQL view and
copy the SQL statement. Maybe paste it into a Word document for holding.

Then open up your current query in SQL view. Make the Where clause something
like the following:

WHERE [Patient ID No] Not In ( SQL statement from above here )

Make sure that there is only one semi-colon ; it the SQL statement. It
should be at the end.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,

I have an Access database and I'm creating a query in design view. I'd like
to bring up a list of all patients who take only aspirin - no other
medication at all. So I've dragged down the Patient ID No, then the
medication No. Aspirin is number 5. So I've put '5' in the criteria space and
when run, it brings up all the patients who take aspirin.
But what would I put in the criteria space to bring up those who take the
aspirin and nothing else?
Many thanks.

 

[Liverlass3981]

Many thanks Karl,
I'll see if I can get a good result with your instructions.
Best regards.