Select Query Multiple Criteria (1 Viewer)

[wlc-cw]

Hello,

I feel silly asking for help with such a simple concept, but everything I try doesn't seem to work!

I'm a college student completing a Database Design unit, so I apologise for such a simple and potentially stupid question.

I have a table called "Vaccinations":

Vaccine_ID​	Patient_ID​	Vaccine_Type​
1​	3​	1st Dose​
2​	3​	2nd Dose​
3​	3​	Booster​
4​	6​	1st Dose​
5​	6​	2nd Dose​

*This is of course a simplified version of the actual table, as only the attributes/fields shown above are needed to express my point. Patient_ID is a foreign key.

For this query, I want to only display patients who are fully vaccinated. I therefore need to enter three Criteria: "1st Dose", "2nd Dose" and "Booster". There seems to be tons of information on how to match one criteria or another - but very little information on how to match multiple criteria before displaying a result.

My desired outcome: using records from the example table above, my "Fully Vaccinated Patients" query (containing the same three fields, with the Vaccine_Type hiddden) would only display Patient_ID 3, as this patient has all three variants of the Vaccine. Patient_ID 6 wouldn't be displayed as they've only had 2 out of 3 total vaccines.

I've tried entering "1st Dose" And "2nd Dose" And "Booster" - no records display whatsoever
I've tried entering In("1st Dose, "2nd Dose", "Booster") - no records display whatsoever
I've tried including Vaccine_Type three times (in the query Design View) and entering each of the three variants separately in each Vaccine_Type field. This yielded the same results as the previous two methods.

Can this be done? If so, how?

Thanks in advance for your time and support!

Is it possible to only display Patient_IDs for patients who have had all three Vaccine_Types?

 

[Gasman]

Group by PatientID, Count on Vaccine_Type > 2

 

[wlc-cw]

Group by PatientID, Count on Vaccine_Type > 2

Thanks very much for your reply.

I've successfully learned how to Group By and applied this to the Patient_ID, but by adding the Count variable in the Criteria box like this:

Count(>2)

Results in the error:

Data type mismatch in criteria expression.

.
I've researched the error and it seems the data types don't match - but this doesn't make sense, as the Patient_ID and the >2 are both numbers?

I think I understand what you're trying to do: instead of ensuring a patient has all three vaccinations, you're simply ensuring they've had more than two.

 

[Gasman]

No, you place entry of Count(Vaccine_Type) in the top row, and in the criteria row enter > 2
Then change to the SQL window and see what is produced.

Use the Query GUI and select the Totals icon

SELECT Transactions.Description, Count(Transactions.Amount) AS CountOfAmount
FROM Transactions
GROUP BY Transactions.Description
HAVING (((Count(Transactions.Amount))>2));

Post that back here if it still does not work.

 

[plog]

If you trust your data, which I don't suggest you ever do, just find those with a booster record. Don't even mess with the others types

Since I don't trust people to not have 3 'First Dose' records, I'd use a sub query.

SELECT Patient_id,   Vaccine_Type
FROM Vaccinations
WHERE Vaccine_Type IN ('First', 'Second', 'Booster')
GROUP BY Patient_id, Vaccine_Type

Then use that query as the datasource fir another query and find those with a COUNT equal to 3

 

[Gasman]

Good advice, but in the UK (and I have no idea where the OP resides) we have people who are particularly vunerable having a 4th jab?
I had such a passenger the other day in the community car, and it surprised me, as I did not even realise 4th jabs were in effect?