How to design a query?

[BittenApple]

Hello team,

I have data as this:

Name vaccination date
Jorge dtap 06/05/2022
Jorge tpv 06/05/2022
Angela dtap 06/07/2022
Angela tpv 06/07/2022
Angela MMR 06/07/2022

I need to make sure each person has three vaccinations. How can I reach there?

I appreciate your response.
Bittenapple

[HansV]

Assuming that each person's vaccinations are unique:

SELECT [Name], Count([Vaccination]) AS NumberOfVaccinations
FROM [tablename]
GROUP BY [Name]
HAVING Count([Vaccination])<3

This should return the names of the persons who have received fewer than three vaccinations.

[xps35]

The reply of Hans looks oké, but I wonder, should the requirement not be that each person has 3 different vaccinations?

[HansV]

That had occurred to me (see the first sentence of my previous reply). Let's see what BittenApple replies.

[BittenApple]

Hello Hans and Peter,
Thanks for your response!!!
Members can have many vaccination such hip, hpv, mmr, dtap and mmr. I am looking for members who have have all three hpv, dtap and mmr only.
Regards,
Bittenapple

[HansV]

That is not what you asked in your first question!
Try this:

SELECT DISTINCT [TableName].[Name] FROM [TableName] WHERE EXISTS (SELECT T.[Name] FROM [TableName] AS T WHERE T.[Name]=[TableName].[Name] AND T.[Vaccination]='DTAP') AND EXISTS (SELECT T.[Name] FROM [TableName] AS T WHERE T.[Name]=[TableName].[Name] AND T.[Vaccination]='TPV') AND EXISTS (SELECT T.[Name] FROM [TableName] AS T WHERE T.[Name]=[TableName].[Name] AND T.[Vaccination]='MMR')

[BittenApple]

Hello Hans,
Thank you for your response.
I need to try this to see how it goes.
Do you have a simpler solution?
I am so grateful to you.
Bittenapple

[HansV]

There are several ways to do this, but they all result in a rather lengthy SQL string since you want to check the existence of three different values.
For example:

SELECT DISTINCT [TableName].[Name] FROM [TableName] WHERE DCount("*", "[TableName]", "[Name]='" & [TableName].[Name] & "' AND [Vaccination]='DTAP'")>0 AND DCount("*", "[TableName]", "[Name]='" & [TableName].[Name] & "' AND [Vaccination]='TPV'")>0 AND DCount("*", "[TableName]", "[Name]='" & [TableName].[Name] & "' AND [Vaccination]='MMR'")>0

[BittenApple]

Hello Hans,
I remembered this syntax.
What else if you please give me a couple more?
I can get ideas.

I am grateful to you.
Bittenapple

[HansV]

Another one:

SELECT DISTINCT TableName.Name FROM (TableName INNER JOIN TableName AS TableName_1 ON TableName.Name = TableName_1.Name) INNER JOIN TableName AS TableName_2 ON TableName_1.Name = TableName_2.Name WHERE TableName.Vaccination="DTAP" AND TableName_1.Vaccination="TPV" AND TableName_2.Vaccination)="MMR"

[HansV]

And another one:

SELECT DISTINCT [TableName].[Name] FROM [TableName] WHERE [TableName].[Name] In (SELECT [Name] FROM [TableName] WHERE [Vaccination]='DTAP') And [TableName].[Name] In (SELECT [Name] FROM [TableName] WHERE [Vaccination]='TPV') And [TableName].[Name] In (SELECT [Name] FROM [TableName] WHERE [Vaccination]='MMR')

[BittenApple]

Thank you