Help with Query!!! (Interesting Problem)

[pinecrest515]

Dear Forumers,

I figured out that my previous MS Access SQL statement, which I thought solved a problem, doesn't:

Suppose I have the following table:

First_Name Last_Name Age_Group Vaccinated Age
John Smith Adult Yes 30
Mary Smith Child Yes 5
Casey Smith Child No 12
Joe Smith Child No 6


Sam Walton Adult Yes 31
Thomas Walton Child No 15
John Walton Child Yes 5
Mary Scott Adult Yes 56
Andrew Johnson Adult Yes 55
Bryon Johnson Child No 12


I am trying to query for a list of Adults that have all their children (who have same last name) either Vaccinated or over the age of 10.

Ideally, the query should output the following:
First_Name Last_Name Age_Group Vaccinated Age
Sam Walton Adult Yes 31
Andrew Johnson Adult Yes 55

Sam Walton is returned, because he has 1 child over 15 and 1 child vaccinated.
Andrew Johnson is returned, because although his child is not vaccinated, the child is over 10 years old.
John Smith is not returned, because although 2 of his children are either vaccinated or over 10, the third child is not vaccinated and under 10.
Mary Scott is not returned, because she has no children.


Currently, my query finds a list of Adults that have at least one child vaccinated or one child over 10. If an adult has one child under 10 who is not vaccinated, but has another child over 10 or vaccinated, then the query will still return the adult's record. This is not robust - I would like only adults who have all their children either vaccinated or over 10.


My current, un-robust query:
SELECT DISTINCT
A.*
FROM
[Table1] AS [A]
INNER JOIN
[Table1] AS [B]
ON [A].[Last_Name] = [B].[Last_Name]
WHERE
[A].[Age_Group] = 'Adult'
AND [B].[Age_Group] = 'Child'
AND
([B].[Vaccinated] = 'Yes' OR [B].[Age] > 10 )

Currently, my un-robust query is outputting the following:
First_Name Last_Name Age_Group Vaccinated Age
Sam Walton Adult Yes 31
John Smith Adult Yes 30
Andrew Johnson Adult Yes 55



How should I best construct this query to achieve the desired results?

Thanks in advance for your reply!

[weekend00]

I think I gave reply for your similar post yesterday, didn't I?

[weekend00]

first step, find out the last names under 10 and not vaccinated.
second, exclude these last names.

Please refer to yesterday's post for more details.

[pinecrest515]

Thanks weekend! Your suggestion helps the sample query to a good extent! Appreciate it! However, I am still running into some additional complexities with my actual query.

[weekend00]

two step in one query:

select table1.* from table1 left join
(select distinct last_name from table1 where age<=10 and vaccinated="No")
as b on table1.last_name=b.last_name where b.last_name is null and age_group="Adult"