Usin "AND" criteria on the same field

[glorber]

I have a table which has two field: "patient ID" and "drug name"
I am trying to create a query which will return all the patiend who use 2 specific drugs. If I use the OR criteria I get some results but if I use the And criteria under the same field I get no results though there are patients who use those 2 drugs.
For example: can I find out what patient use both drug "b" & "c" (001 & 003)?
patient ID Drug name
001 a
001 b
001 c
002 a
002 b
003 b
003 c

 

[Jack Daniels]

Well I think its like this:
Criteria 1 on the first line
Criteria 2 on the second line

I think it look like what you want is OR
If You want to return both.....

Jack

 

[AncientOne]

Unfortunately, the previous solution will return any records containing either Drug B or Drug C. You need to create a many to many relationship between patients and drugs. This is because many patients can have a particular drug , and many drugs may be taken by a particular patient. You need a Patient table, a drugs table and a link table which joins the two primary keys. Not as bad as it sounds and there are literally thousands of examples to be found by searching this forum.

 

[Pat Hartman]

You can use a totals query to identify the patients. Query1 identifies the patients taking either drug and the totals query selects only those users using both drugs.

Query1:
Select distinct PatientID, DrugID From tblDrugs
Where DrugID = 1 or DrugID = 2;

Query2:
Select PatientID Count(*) As DrugCount
From Query1
Where Count(*) > 1
Group By PatientID;

 

[AncientOne]

That's a method which I considered and rejected in favour of establishing a politically-correct database structure. Ah well, sometimes you just can't win...

 

[glorber]

First, thanks for the various tips.
Actually, I was considering the same general idea (2 queries), but I was not sure how to write the code.
I will try out your suggestion and hope it works (since I am new with access, I am not too sure as to where and how to place the code - but I will try to figure that out on my own).
Thanks again!

 

[Jon K]

Query1:-
SELECT DISTINCT PatientID, DrugName
FROM tblDrugs
WHERE DrugName='b' Or DrugName='c';

Query2:-
SELECT PatientID
FROM Query1
GROUP BY PatientID
HAVING Count(*)>1;


Run the second query.
.

 

[AncientOne]

Tip:
you don't need any code.

just compose two queries. Access creates the code from the entries you make in the grid. The first is a straightforward select query as explained by your first respondent. The second query uses the first query's fields. If you go to the VIEW menu whilst in query design and check the menu item 'Totals', an extra row of aggregate functions appears in the grid. Select 'Group by' for the Patient column and 'Count' for the drugs column. In the Drugs criteria column, put 2. This will return all patients who are on both drugs you specified in the first query.

 

[glorber]

I eventually figured out where to place the code and though there were some small bugs (apparently 'WHERE clause' doesnt allow aggregate phrases but the 'HAVING cluase' does) your suggestions worked - and for that I am very grateful!
ancient one - you suggested I use two tables with a "many to many" relationship between them. Will that enable me to get the same result using only one query?

 

[AncientOne]

Yes, it would enable you to run the query in one hit.

The reasons I rejected the solution of using two queries was because one of the aims in normalizing a database is to prevent repetition of data entry. With your table containing both the patient's name and the patient's drug in the same row, duplication of some sort is inevitable if the same patient is on more than one drug. Every time you enter the patient's details and the drug under this scenario, you have to get them the same as they originally were. There is potential for mistakes there. You may get a name or a number wrong or some small variation may creep in eg, calling someone William Smith in one row and the same person Bill Smith in another row . With the data in separate tables, the drug name and its associated data has to be entered only once and errors are less likely. Likewise with the patient: he/she will always only have one set of data associated with him/her and that will all be linked by a unique primary key.

However, I have to admit that I am not an true Access programmer, just a self-taught amateur. If you receive advice from the prime authority in this forum that a certain course of action is acceptable, you might be well-advised to follow her advice than that of a much less able advisor.

 

[Jon K]

ancient one - you suggested I use two tables with a "many to many" relationship between them. Will that enable me to get the same result using only one query?

Using two tables with a "many-to-many" relationship is not normalized structure.


You should use three tables:-
A patients table recording the personal details of each patient, the PatientID being the primary key.

A drugs table recording details of each drug, the DrugID being the primary key.

A linking table that records the drugs used by each patient. The primary key can be the PatientID and DrugID plus a PrescriptionID. This table can also contain other details such as the quantity of each drug prescribed.

The three tables are related by two "one-to-many" relationships:-
Patients table and linking table (related by PatientID)
Drugs table and linking table (related by DrugID)


So the linking table is virtually very similar to your table of:-

Patient ID 	Drug Name
001 		a
001 		b
001 		c
002 		a
002 		b
003 		b
003 		c

but with the Drug Name field to be replaced by the DrugID field (and the adding of the PrescriptionID field.)

Hence to "return all the patients who use 2 specific drugs", you still have to use two queries based on the linking table.


Pat posted a very good example in this thread regarding using a linking table to resolve a many-to-many issue:-
http://www.access-programmers.co.uk/forums/showthread.php?t=62564

.

 

[AncientOne]

if you refer to a previous answer I gave, you will see that I mention a join table. In this answer, I am stating that Patients and drugs need to be in separate tables, nothing else.

 

[EMP]

Jon K,
You have made a very good point.

Many-to-many is an issue, not a solution. The solution to the issue is to set up two one-to-many relationships.


glorber,
The queries work because a semi-solution has been set up in your database. That is one one-to-many relationship has been set up.

I have seen many databases that use a semi-solution like that when detailed information about the other one-side is never needed.

 

[Pat Hartman]

Sorry about the Where instead of the Having. That's what happens when ya' type out a suggestion. Sometimes it isn't 100%. Again, please accept my appoligies.

When I suggested the two query solution, I assumed that you had a proper normalized structure - Patients --> PatientDrugs <-- Drugs with the PatientDrugs table being the relation table that makes the many-to-many relationship between Patients and Drugs real. I assumed this because the original question referenced only the PatientID and DrugID which would be the primary fields of the relation table. I used only the PatientDrugs and Drugs tables in the query for simplicity. In the real world, the second query might need to be joined to the Patients table in order to obtain Patient details. I think OldSoftOne took my suggestion to mean that I thought that Patients and PatientDrugs were combined in a single table thereby duplicating patient data.

As someone already mentioned, in most relational database, including Access, a many-to-many relationship cannot be implemented directly and is always implemented as two 1-many relationships.

The two tiered quey is more efficient in Access databases because Jet doesn't optimize subselects properly. There is nothing wrong with using two queries. It doesn't violate any design or good practice rules. It simply gets past an optimization issue with Jet.

 

[glorber]

So Basically, if I understand correctly, other than good database management, using a many-to-many relationship does not solve the need to create two queries.
Since I need to run checks on multiple drug combinations, using a two-query solution is not ideal since each time I have to go back to both queries and change the drug names that I want to check.
Is it possible to construct the DB in a way that would enable running just one query?

 

[Jon K]

You can put two combo boxes on a form for the user to select the drug names and, in the first query, reference the combo boxes for the drug names:-

SELECT DISTINCT [PatientID], [DrugName]
FROM tblDrugs
WHERE DrugName=[Forms]![Form1]!cboFirstDrug Or DrugName=[Forms]![Form1]!cboSecondDrug;

and run the second query from a command button on the form. This way, you don't need to alter the drug names in the query. See database attached.


In some versions of Access, you can use one query like this:-

SELECT [PatientID]
FROM (SELECT DISTINCT [PatientID], [DrugName]
FROM tblDrugs
WHERE DrugName=[Forms]![Form1]!cboFirstDrug Or DrugName=[Forms]![Form1]!cboSecondDrug)
GROUP BY [PatientID]
HAVING Count(*)>1;

However, when the query is saved, Access may change the brackets to square brackets and add an alias for the subquery. So if you subsequently edit the query in SQL View, you must change it back to its original format.
.

 

[Pat Hartman]

Use the subselect that someone posted earlier. It will probably be fine. If it is too slow, then you can worry about finding a different solution.

 

[glorber]

Once again, many thanks for your assistance.
Jon K - I downloaded your sample DB and I will study it's structure and code. It seems to resolve my problem in an elegant way.
Thanks!