How to design a query?

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

How to design a query?

Post by 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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to design a query?

Post by 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.
Best wishes,
Hans

xps35
NewLounger
Posts: 14
Joined: 27 Sep 2022, 07:22
Location: Schiedam, NL

Re: How to design a query?

Post by xps35 »

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

Peter

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to design a query?

Post by HansV »

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

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: How to design a query?

Post by 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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to design a query?

Post by 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')
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: How to design a query?

Post by 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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to design a query?

Post by 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
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: How to design a query?

Post by 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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to design a query?

Post by 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"
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to design a query?

Post by 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')
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: How to design a query?

Post by BittenApple »

Thank you 🙏