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.

 

[KenSheridan via AccessMonster.com]

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

 

[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.

 

[KenSheridan via AccessMonster.com]

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]

 

[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.

 

[KenSheridan via AccessMonster.com]

When Access doesn't recognize something in a query as a valid object name it
treats it as a parameter, which would suggest that PatientID is not the
correct name of a column in the Medication table, e.g. it might really have a
space or an underscore character in it Patient ID or Patient_ID. However,
Medication also features in the outer query under the alias T1 and there is
again a reference to the PatientID column. It may be that its wrong in both
cases of course and you are only seeing the first of what would be two
parameter prompts if you entered a value at the prompt.

One way we can easily confirm the column names is to allow the query designer
to build two simple queries; you don't have to open them or save them, they
are purely so we can see the column names. So with both the Medication and
PatientName tables open the query designer and add the PatientID column to
the design grid, then switch to SQL view and you should see:

SELECT [Medication].[PatientID] FROM [Medication];

and:

SELECT [PatientName ].[PatientID] FROM [PatientName ];

for each of the queries. You can copy the SSL and post in back here if you
don't see any obvious disparity in the column names yourself. That doesn't
mean we'll see any disparity of course, but at least the dog will be able to
se the rabbit.

Ken Sheridan
Stafford, England

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

[quoted text clipped - 52 lines]

 

[KenSheridan via AccessMonster.com]

Oops! That should have been SQL of course.

Ken

 

[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.

 

[KenSheridan via AccessMonster.com]

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
correct name of the field in the table, and if it differs change each
occurrence of PatientID in the SQL statement to whatever the actual field
name in the table is.

Ken Sheridan
Stafford, England

 

[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,

 

[KenSheridan via AccessMonster.com]

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]

 

[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