Help! Very interesting query problem

[pinecrest515]

Dear Forumers,

Thank you for your previous help with my query. I have relatively little exp with Acces, so appreciate all your help. I’ve been using the same simplified example to ask about query questions, even though in real life the sensitive data and query situation may be more complicated and involve more data.

Suppose I have the following table, where different families (with same last name) are listed. Each family has one adult and zero to many children.

First_Name Last_Name Age_Group Vaccinated Age Doctor Citizenship
John Smith Adult Yes 30 Hibbert Yes
Mary Smith Child Yes 5 Nikos Yes
Casey Smith Child No 12 Nikos Yes
Joe Smith Child No 6 Nikos Yes
Sam Walton Adult Yes 31 Hibbert No
Thomas Walton Child No 15 Nikos Yes
John Walton Child Yes 5 Nikos No
Mary Scott Adult Yes 56 Nikos Yes
Andrew Johnson Adult Yes 55 Hibbert No
Bryon Johnson Child No 12 Hibbert Yes
Homer Simpson Adult Yes 38 Hibbert Yes
Bart Simpson Adult Yes 9 Hibbert Yes
Lisa Simpson Adult Yes 8 Hibbert Yes


I am trying to query for a list of families (Adults with their respective Children) where in each family all their children (who have same last name) are either Vaccinated or over the age of 10 or has same doctor as parent, AND where adults have citizenship.


The following query will give me families (Adults and their respective children) where in each family all children are either vaccinated or over the age of 10 or has same doctor as parent.
SELECT A.*
FROM table1 AS A LEFT JOIN
(SELECT DISTINCT C.[Last_Name] FROM Table1 AS C INNER JOIN Table1 AS D ON C.[Last_Name]=D.[Last_Name] WHERE C.Age_Group='Adult' And D.Age_Group='Child' And (D.vaccinated="NO" And D.age<10 AND C.[Doctor] <> D.[Doctor]))
As B ON A.[Last_Name] = B.[Last_Name]


WHERE a.family='Yes' And b.last_name Is Null;



The following query will give me families (Adults and their respective children) where the adult has citizenship.
SELECT DISTINCT E.*
FROM Table1 AS E LEFT JOIN
(
SELECT DISTINCT K.[Last_Name] FROM Table1 AS J INNER JOIN Table1 AS K ON J.[Last_Name]=K.[Last_Name]
WHERE J.[Age_Group] = 'Adult' And K.[Age_Group]= 'Child' AND J.[Citizenship] = 'Yes'
)
AS F ON E.[Last_Name]=F.[Last_Name] Where (F.[Last_Name] Is Not Null)

How do I combine these two queries to get families where list of families (Adults with their respective Children) where in each family 1) all their children (who have same last name) are either Vaccinated or over the age of 10 or have the same doctor as parent AND 2) where adults have citizenship?

An inner join of the two above queries? How would I do it? Is there an easier way?

[weekend00]

you may add conditions to the first query as follow:
SELECT A.*
FROM table1 AS A LEFT JOIN
(SELECT DISTINCT C.[Last_Name] FROM Table1 AS C INNER JOIN Table1 AS D ON C.[Last_Name]=D.[Last_Name] WHERE C.Age_Group='Adult' And (c.cityzenship=false or D.Age_Group='Child' And D.vaccinated="NO" And D.age<10 AND C.[Doctor] <> D.[Doctor]))
As B ON A.[Last_Name] = B.[Last_Name]
WHERE a.family='Yes' And b.last_name Is Null;

[pinecrest515]

Thanks Weekend, appreciate the suggestion.

However, the proposed solution does not completely solve. The Adult being a citizen logic should be taken separately from child being vaccinated, under age 10, or having same doctor as parent. The reason is that, if adult is NOT a citizen but all his/her children are either vaccinated, under 10, or have same doctor as adult, then this family should NOT be returned.

Is there another way....like using an inner join of two queries (that are themselves joins)?

[weekend00]

I don't have data to test the logic and I could not figure what is not right.

if an adult is not citizen, this family will be excluded regardless other conditions, right?

[pinecrest515]

Yes, that's correct!

[weekend00]

So I think the query should work. but I have no way to test it.