Query criteria

[Liverlass3981]

Hi,

I'd like to create a query to bring up a list of people who only take
metformin (medication) for their diabetes. They may well take other
medications for their diabetes but I want only those who take metformin ONLY!

So far in my query I have 'Patient ID', 'Medication#' (the number I'm using
is '4') and 'Diabetic' - this is whether the medication taken is diabetic or
not and is a yes/no field.

I've put '4' in the criteria for 'Medication#' and 'yes' in the criteria for
'Diabetic'. When I run the query, it brings up all of the patients who are
taking '4' medication. But I want to bring up those that are only taking
metformin (4) alone for their diabetes.

I would be very grateful if someone could advise me what I should add or
change to my query.

 

[beginner]

hello,

how many fields do you have and what are they?
trying to understand your table.

 

[Ken Snell [MVP]]

You'll need a separate query that will be used to exclude patients taking
medications other than number 4, which then will be used in your final
query.

Create this query (name it qryMedication_Not4):

SELECT DISTINCT [Patient ID], [Diabetic]
FROM YourTableName
WHERE [Medication#] <> '4';


Now create your final query:

SELECT YourTableName.[Patient ID],
YourTableName.[Medication#],
YourTableName.[Diabetic]
FROM YourTableName
LEFT JOIN qryMedication_Not4
ON YourTableName.[Patient ID] =
qryMedication_Not4.[Patient ID] AND
YourTableName.[Diabetic] =
qryMedication_Not4.[Diabetic]
WHERE qryMedication_Not4.[Patient ID] IS NULL AND
YourTableName.[Diabetic] = 'Yes' AND
YourTableName.[Medication #] = '4';

 

[Liverlass3981]

Hi,
The table has the fields 'PatientID', 'Medication#' 'Dosage' and 'Diabetic'
'Other'

So I enter the patient number, the number of the medication he/she is
taking, the dosage and I tick whether the medication is a diabetic medication
or other.

One patient can take many medications.

 

[Liverlass3981]

Ken,

Thanks for your reply. I am however, fairly new to Access and I cannot
understand the answer you gave to me. Would it be possible for you to give me
a set of simple instructions, telling me exactly which fields to drag down
into the query and then what I should put exactly in the criteria row and
under which field in the query. I would really appreciate this. This is the
only way I can do queries.

I do have a main table with one record for each patient and also a
treatments table.
Hope you can still help simplify things for me.

You should be able to do it with a subquery to exclude those patients who are
diabetic and taking medication other than metformin. Assuming that you have
a Patients table (with one row per patient) and a Treatment table, e.g.

SELECT FirstName, LastName,
"Metformin" AS Medication, Dosage
FROM Treatment AS T1
INNER JOIN Patients
ON Patients.PatientID = T1.PatientID
WHERE Diabetic AND [Medication#] = 4
AND NOT EXISTS
(SELECT *
FROM Treatment As T2
WHERE T2.PatientID = T1.PatientID
AND Diabetic AND [Medication#] <> 4);

The subquery is correlated with the outer query on PatientID, giving each
instance of the Treatment table a different Alias T1 and T2 to differentiate
between them. The subquery will only return row(s) if a row exists for the
patient in Treatment where the value in the Diabetic column is TRUE and the
medication is other than metformin. Consequently the outer query will only
return those patients who are diabetic and whose sole medication for diabetes
is metformin as the NOT EXISTS predicate would then evaluate to TRUE.

Even if you don't have a Patients table the query would still work if the
outer query is based solely on Treatment AS T1.

Ken Sheridan
Stafford, England

Hi,

I'd like to create a query to bring up a list of people who only take
metformin (medication) for their diabetes. They may well take other
medications for their diabetes but I want only those who take metformin ONLY!

So far in my query I have 'Patient ID', 'Medication#' (the number I'm using
is '4') and 'Diabetic' - this is whether the medication taken is diabetic or
not and is a yes/no field.

I've put '4' in the criteria for 'Medication#' and 'yes' in the criteria for
'Diabetic'. When I run the query, it brings up all of the patients who are
taking '4' medication. But I want to bring up those that are only taking
metformin (4) alone for their diabetes.

I would be very grateful if someone could advise me what I should add or
change to my query.

 

[Liverlass3981]

Hi Ken,
many thanks for responding to me. I had a go following your instructions and
came up with:

SELECT PatientInitials,
"Metformin" AS Medication, Dosage
FROM Medication AS T1
INNER JOIN PatientName
ON PatientName.PatientID = T1.PatientID
WHERE Diabetic AND [Medication#] = 4
AND NOT EXISTS
(SELECT * FROM Medication As T2
WHERE T2.PatientID = T1.PatientID
AND Diabetic AND [Medication#] <> 4);

However, when I ran it, a box came up asking me to enter parameter value and
'T2.PatientID' was above the space to enter the value.

I've clearly done something wrong and I do hope you can continue to help me.
Kind regards.

I'm afraid you can't construct a subquery in design view. You could
construct the main (outer) query via the design grid but you'd still have to
enter the subquery in SQL view, so it makes more sense to enter the whole
thing in SQL view. Writing SQL does look a bit scary to all of us when we
first start, but its actually quite simple to do once you've dipped your toes
in the water:

1. Open the query designer but don't add any tables.

2. Switch to SQL view from the menu or toolbar.

3. Copy the SQL I sent you and paste it into the SQL window in place of
what's there already.

4. Change the table and column (field) names to your real table and column
names, but don't change the aliases T1 and T2. In the SELECT clause of the
outer query, i.e. the list of column names after the first 'SELECT', add the
names of any other columns you want to show in the results, following each
column name apart from the last one with a comma.

5. If any columns are in more than one of the tables precede it by the table
name in the case of the Patients table, or the alias T1 in the case of the
'Treatments' (or whatever is the real name of your table containing the
Medication# and Diabetic columns) table.

6. If any table or column names include spaces or special characters wrap
the name of the table in square brackets as I've done with [Medication#]
because of the hash character. If in doubt wrap all the table and column
names in brackets, but not the aliases T1 and T2.

7. Save the query and open it. If the wind is in the right direction it
should give you what you are looking for.

Good luck, and let me know how you get on.

Ken Sheridan
Stafford, England

Ken,

Thanks for your reply. I am however, fairly new to Access and I cannot
understand the answer you gave to me. Would it be possible for you to give me
a set of simple instructions, telling me exactly which fields to drag down
into the query and then what I should put exactly in the criteria row and
under which field in the query. I would really appreciate this. This is the
only way I can do queries.

I do have a main table with one record for each patient and also a
treatments table.
Hope you can still help simplify things for me.

You should be able to do it with a subquery to exclude those patients who are
diabetic and taking medication other than metformin. Assuming that you have

[quoted text clipped - 43 lines]

I would be very grateful if someone could advise me what I should add or
change to my query.

 

[Liverlass3981]

Hi Ken,

I don't know what I've done wrong and I'm obviously a bad beginner as I
don't understand your answer - the code you gave me is SQL isn't it?

Could you tell which bits I need to change to make the query work?

Kind regards.

 

[Liverlass3981]

Hi Ken,

Thanks a lot for your reply. I'm still getting the prompt box as before with
'T2.PatientNo' when the query is run. I had:

SELECT PatientInitials,
"Metformin" AS Medication, Dosage
FROM Medication AS T1
INNER JOIN PatientName
ON PatientName.PatientNo = T1.PatientNo
WHERE Diabetic AND [Medication#] = 4
AND NOT EXISTS
(SELECT * FROM Medication As T2
WHERE T2.PatientNo = T1.PatientNo
AND Diabetic AND [Medication#] <> 4);

I had a thought - Where I have put 'Medication' for one of the tables (this
is just the medication# and medication name here), should I replace this with
the 'PatMedsDosageFrequency table'? This is the table where the records are
of the medications taken by the patient. The fields here are:
PatientNo, Medication#, MedQuant, Dosagemeasure, Frequency, DiabeticMed
(yes/no format) OtherMed(yes/no format).

Sorry to put this on to you - I really appreciate your help.

kind regards.

 

[Liverlass3981]

Hi Ken,

I hope I'm not asking too much of you but I'd be so grateful if you could
see me through to the end with the query.

Kind regards,

 

[Liverlass3981]

Hi Ken,

The query is working!! I am thrilled! Thank you so much for all of your help
and your patience too. I hope you choose to help me again if I ever need help
again!
I noticed you are from Stafford - I was at Staffordshire uni about 8 years
ago and really enjoyed it!
Best regards.

That sounds like the problem. Try:

SELECT PatientInitials,
"Metformin" AS Medication, MedQuant,
DosageMeasure, Frequency
FROM PatMedsDosageFrequency AS T1
INNER JOIN PatientName
ON PatientName.PatientNo = T1.PatientNo
WHERE DiabeticMed AND [Medication#] = 4
AND NOT EXISTS
(SELECT * FROM PatMedsDosageFrequency As T2
WHERE T2.PatientNo = T1.PatientNo
AND DiabeticMed AND [Medication#] <> 4);

Ken Sheridan
Stafford, England

Hi Ken,

Thanks a lot for your reply. I'm still getting the prompt box as before with
'T2.PatientNo' when the query is run. I had:

SELECT PatientInitials,
"Metformin" AS Medication, Dosage
FROM Medication AS T1
INNER JOIN PatientName
ON PatientName.PatientNo = T1.PatientNo
WHERE Diabetic AND [Medication#] = 4
AND NOT EXISTS
(SELECT * FROM Medication As T2
WHERE T2.PatientNo = T1.PatientNo
AND Diabetic AND [Medication#] <> 4);

I had a thought - Where I have put 'Medication' for one of the tables (this
is just the medication# and medication name here), should I replace this with
the 'PatMedsDosageFrequency table'? This is the table where the records are
of the medications taken by the patient. The fields here are:
PatientNo, Medication#, MedQuant, Dosagemeasure, Frequency, DiabeticMed
(yes/no format) OtherMed(yes/no format).

Sorry to put this on to you - I really appreciate your help.

kind regards.

Yes, it is SQL. It looks like it’s the name of the PatientID field which is
wrong in the query, so you need to check in table design view that that's the

[quoted text clipped - 17 lines]

Ken