Combine two queries

[chillay]

I am trying to combine 2 queries.

The following query shows the HOH (Head of House hold) with no children:


SELECT HOH.HOHID, HOH.Lastname, HOH.Firstname, (Select Count (*) From Children

Where HOH.HOHID = Children.HOHID) AS TotalChildren
FROM HOH
WHERE (Select Count (*) From Children
Where HOH.HOHID = Children.HOHID) = 0
ORDER BY LastName;



The following query shows the HOH (Head of House hold) with no spouse:

SELECT HOH.HOHID, HOH.Lastname, HOH.Firstname, (Select Count (*) From Spouse

Where HOH.HOHID = Spouse.HOHID) AS Spouse
FROM HOH
WHERE (Select Count (*) From Spouse
Where HOH.HOHID = Spouse.HOHID) = 0
ORDER BY LastName;

I want to create a query that shows the HOH (Head of House hold) with no spouse or chidren:

Thank you

Chillay

 

[PHV]

A starting point:
SELECT HOH.HOHID, HOH.Lastname, HOH.Firstname, Count(Children.HOHID) AS TotalChildren, Max(IIf(IsNull( Spouse.HOHID),0,1)) AS Spouse
FROM (HOH LEFT JOIN Children ON HOH.HOHID = Children.HOHID)
LEFT JOIN Spouse ON HOH.HOHID = Spouse.HOHID
GROUP BY HOH.HOHID, HOH.Lastname, HOH.Firstname
HAVING Count(Children.HOHID) = 0 Or Max(IIf(IsNull( Spouse.HOHID),0,1)) = 0
ORDER BY LastName;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[chillay]

That definitely helps. I am still playing with it. Thank you PH. You've got skills!!

Chillay