Help!!! Urgent Access SQL Query Question

[pinecrest515]

Dear Forumers,

I need help on an Access SQL statment:

Suppose I have the following table:

First_Name Last_Name Age_Group Vaccinated
John Smith Adult Yes
Joe Smith Child No
Mary Smith Child No
Sam Walton Adult Yes
Thomas Walton Child Yes
John Walton Child Yes
Casey James Adult Yes
Mary Scott Adult No
Andrew Johnson Adult Yes
Bryon Johnson Child Yes


I am trying to query for a list of all Adults whose children (who have same last name) have been Vaccinated.

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

How should I best construct this query?
I only want the Parent, Adult records, not child records.

Something like

Select Distinct * From Table
Where Age_Group = ‘Adult’ AND has one or more Child with same ‘Last_Name’ AND each child is vaccinated




Right now, I am thinking along these lines:

Select Distinct * From t1.Last_Name, t2.Last_Name, t1.Age_Group, t1.Vaccinated
FROM
(table1 t1
JOIN
table1 t2)
ON
t1.Last_Name = t2.Last_Name
Where
t2.Vaccinated = ‘Yes’

Based on syntax above, getting an error on Access saying there is a syntax error around FROM clause, can you, based on your extensive SQL experience, shed light into my error?

Thanks!

[weekend00]

query 1: find all last name with no vaccinated:
select distinct last_name from table1 where vaccinated="NO"

2 find the adult with last name not in query 1

select a.* from table1 as a left join (
select distinct last_name from table1 where vaccinated="NO"
) as b on a.last_name=b.last_name where a.age_group="Adult" and b.last_name is null

you don't need to save query 1, it is only for helping you to understand the logic.
save query 2 and run it.