Medication queries design help (1 Viewer)

[RxExec]

I am an Access Beginner,
I have a medications database based on 3 tables...tblPatients, tblPatientMedications and tblMedications. I can query for patients using a single medication.

I need to query for patients who use:
1. both MedA AND MedB
2. both MedA AND MedB but NOT MedC
3.MedA OR MedB but NOT MedC

I've been trying...
SELECT DISTINCT tblPatients.FirstName, tblPatients.LastName, tblPatients.City
FROM tblPatients INNER JOIN (tblMedications INNER JOIN tblPatientMedications ON tblMedications.Med_ID = tblPatientMedications.Med_ID) ON tblPatients.Patient_ID = tblPatientMedications.Patient_ID
WHERE (((tblMedications.MedName)="Lantus" And (tblMedications.MedName)="Januvia"))
ORDER BY tblPatients.City;

I would like to end up with a parameter query that staff could fill

Thanks for your help

 

[DavidAtWork]

A simple way of doing this would be to create a combo or list box and the user can select which query to run and you create 3 separate queries and run whichever is chosen. Your combo or list box values could be the same as your 3 options above.

Another more complex way would be to use what value is selected to create a criteria string and you build a sql string similar to your select statement, but your WHERE clause will be a string variable set by user selection

David

 

[namliam]

Another more complex way would be to use what value is selected to create a criteria string and you build a sql string similar to your select statement, but your WHERE clause will be a string variable set by user selection

This, though more complex it is more robust and more powerfull

Building the SQL dynamically though can be very daunting.

 

[jdraw]

I suggest you review Martin Green's site which uses techniques similar to those provided by David and namliam to build a dialog form.

 

[RxExec]

Thanks to David. Namiliam and jdraw. It looks like the dialog form is the way to go and thanks again to jdraw for the link to the tutorial.

I need to start by creating the queries. The SQL I tried in my first post returns nothing. How do I correct that so I can find only patients who are on both "januvia" and "lantus"? and then create another query to find patients who are on both "januvia" and "lantus" but NOT "Aspirin".

Once I get my head around that I can move on to a dialog form...

Thanks Again

 

[jdraw]

There are a number of pieces to your issue.

The dialog form is a form with controls. Martin's tutorial and examples cover that in general terms.

The query involves SQL. That can be assisted by the w3schools sql site
There are samples set up there and you can "play/test" to see the results.

For reference, you may need to have a basic understanding of SQL subqueries. Allen Browne has this covered at this site.

The other part is vba and that usually requires some effort, trial and error.

Follow a few of Martin's samples, especially the use of SQL and vba, and post back if you get stuck.

Also it would be helpful to readers if you told us about your tables and relationships.

 

[namliam]

Since each med will be a seperate record... you will need to "make it one record" in order to be able to do this.
You can do this in one of two ways.... the second being the easiest to code I believe.

Joins

SELECT DISTINCT tblPatients.FirstName, tblPatients.LastName, tblPatients.City
FROM       tblPatients 
INNER JOIN 
         ( 
           Select patient_id 
           From       tblPatientMedications 
           INNER JOIN tblMedications        ON tblMedications.Med_ID = tblPatientMedications.Med_ID
           WHERE      tblMedications.MedName = "Lantus" 
         ) as Lantus ON tblPatients.Patient_ID = Lantus.Patient_ID
INNER JOIN
         (
           Select patient_id 
           From       tblPatientMedications 
           INNER JOIN tblMedications        ON tblMedications.Med_ID = tblPatientMedications.Med_ID
           WHERE      tblMedications.MedName = "Januvia"
         ) as Januvia ON tblPatients.Patient_ID = Januvia.Patient_ID
Left outer join
         ( 
           Select patient_id 
           From       tblPatientMedications 
           INNER JOIN tblMedications        ON tblMedications.Med_ID = tblPatientMedications.Med_ID
           WHERE      tblMedications.MedName = "Aspirin"
         ) as Aspirin  ON tblPatients.Patient_ID = Aspirin.Patient_ID
Where Aspirin.patient_id is null
ORDER BY tblPatients.City;

Counts/sums

SELECT     tblPatients.FirstName, tblPatients.LastName, tblPatients.City
FROM       tblPatients 
INNER JOIN tblPatientMedications ON tblPatients.Patient_ID       = tblPatientMedications.Patient_ID
INNER JOIN tblMedications        ON tblPatientMedications.Med_ID = tblMedications.Med_ID  
Group by   tblPatients.FirstName, tblPatients.LastName, tblPatients.City
having 
           Sum(IIF(tblMedications.MedName = "Lantus" ,1,0) >= 1 
       AND Sum(IIF(tblMedications.MedName = "Januvia",1,0) >= 1 
       AND Sum(IIF(tblMedications.MedName = "Aspirin",1,0)  = 0

 

[RxExec]

Thanks jdraw for the pointers...I'm doing some learning.

namliam, thank you very much for laying that code out for me. I would have struggled for a long time. I'm working at it but I am getting a syntax error (missing operator)with both the join and count method...I'll get it, but if you have a hint?

Thanks Again

 

[namliam]

That is aircode for you...
Missing brackets:

SELECT     tblPatients.FirstName, tblPatients.LastName, tblPatients.City
FROM       tblPatients 
INNER JOIN tblPatientMedications ON tblPatients.Patient_ID       = tblPatientMedications.Patient_ID
INNER JOIN tblMedications        ON tblPatientMedications.Med_ID = tblMedications.Med_ID  
Group by   tblPatients.FirstName, tblPatients.LastName, tblPatients.City
having 
           Sum(IIF(tblMedications.MedName = "Lantus" ,1,0)[COLOR="Red"])[/COLOR] >= 1 
       AND Sum(IIF(tblMedications.MedName = "Januvia",1,0)[COLOR="Red"])[/COLOR] >= 1 
       AND Sum(IIF(tblMedications.MedName = "Aspirin",1,0)[COLOR="Red"])[/COLOR]  = 0

Not quite sure what could be wrong with the Join constuction, must admitt it has been a while I wrote Access SQL... Could be something I am doing "oracle" instead of access way... Sorry

 

[RxExec]

Hi namliam
Still no go with only the added brackets....but it does run with this change to INNER JOIN... Thank You!

SELECT tblPatients.FirstName, tblPatients.LastName, tblPatients.City
FROM tblPatients
INNER JOIN (tblMedications INNER JOIN tblPatientMedications ON tblMedications.Med_ID = tblPatientMedications.Med_ID) ON tblPatients.Patient_ID = tblPatientMedications.Patient_ID
GROUP BY tblPatients.FirstName, tblPatients.LastName, tblPatients.City
HAVING
(((Sum(IIf([tblMedications].[MedName]="Lantus",1,0)))>=1)
AND ((Sum(IIf([tblMedications].[MedName]="Januvia",1,0)))>=1)
AND ((Sum(IIf([tblMedications].[MedName]="ASA",1,0)))=0));

Next I would like to be able for a user to enter the Januvia, lantus and aspirin into a parameter query like below but cant quite figure out how yet....adding a WHERE operator to your code maybe??

SELECT tblPatients.Patient_ID, tblPatients.FirstName, tblPatients.LastName
FROM tblPatients
INNER JOIN (tblMedications INNER JOIN tblPatientMedications ON tblMedications.Med_ID=tblPatientMedications.Med_ID) ON tblPatients.Patient_ID=tblPatientMedications.Patient_ID
WHERE (((tblMedications.MedName)=[What's the first Drug] Or (tblMedications.MedName)=[What's the second Drug] something [What drug would you like to exclude?]))
GROUP BY tblPatients.Patient_ID, tblPatients.FirstName, tblPatients.LastName
HAVING (((Count(*))=2));

Thanks Again

 

[namliam]

the having works the same as a where clause, however if you dynamicaly want to sometimes to this sometimes do that you cant really use parameters as they Always do the same thing

 

[RxExec]

Sorry, I didn't ask that question very well.
The other queries I needed are working with popups asking the user for between 1 and 3 drug names inclusive, utilizing the WHERE clause. Can I adapt your code to have user popups??? Maybe adapting the HAVING or should I be looking at a dialog form...

Thanks Again

 

[namliam]

you can enter the popups the same way you do them with where clauses in the having.

Personly I prefer forms, cause you can allow users to use drop down lists or do validation and all that kind off thing, which you cant do with the popups.

 

[RxExec]

Thanks for the advice ...I'll do forms. Thanks to everyone for the help and especially namliam for hanging in there with me...