Linked Count Queries / One Null Query Blanks Out All The Rest.

Hi all,

Desperate for help on this.

The query below ran like a charm for this years database which was full of entries, but when I did a quick test run for next year with limited entries the 'Temp' table fields were all blanked. All the individual queries, 1 through 9, work fine on their own and work fine in this linked form when there is enough data in the database to cover all the fields.
Apparently if only one query is blank all the fields will be blank.

For instance if there are multiple registered households, table ‘1’ would normally show a count of those households, but if none of those households have children, table '2' which counts children will be blank and in turn blank out all the rest of the fields including table ‘1’.

I've tried the ‘is null, '=0" and ‘nz’ routines on the Count(Tablename.Fieldname)'s, but can't seem ‘to get them to output a value of 0 in the null fields. I must be doing something wrong. Any and all help would be very much appreciated.

SELECT Year(Now()) AS ProjectYear, [1].CountOfClaimID AS Households, [2].CountOfPersonID AS Children, [3].CountOfPersonID AS Participants, [4].CountOfSponsorID AS SponsorsTotal, [5].CountOfHouseholdID AS Sponsored,
[6].FamiliesWithChildren, [7].SponsoredFood, [8].SponsoredGifts, [9].SDSD

INTO Temp

FROM

(SELECT Count(Household.ClaimID) AS CountOfClaimID FROM Household GROUP BY Household.RegStatus
HAVING (((Household.RegStatus)=-1))) AS 1,

(SELECT Count(Members.PersonID) AS CountOfPersonID
FROM Household INNER JOIN Members ON Household.HouseholdID = Members.HouseholdID
GROUP BY Household.RegStatus, Members.Status HAVING (((Household.RegStatus)=-1) AND ((Members.Status) = "Child" ))) AS 2,

(SELECT Count(Members.PersonID) AS CountOfPersonID FROM Household INNER JOIN Members ON Household.HouseholdID = Members.HouseholdID
GROUP BY Household.RegStatus HAVING (((Household.RegStatus)=-1))) AS 3,

(SELECT Count(Sponsors.SponsorID) AS CountOfSponsorID
FROM Sponsors GROUP BY Sponsors.SponsorStatus HAVING (((Sponsors.SponsorStatus)=-1))) AS 4,

(SELECT Count(Sponsorship.HouseholdID) AS CountOfHouseholdID FROM Sponsors INNER JOIN
Sponsorship ON Sponsors.SponsorID = Sponsorship.SponsorID GROUP BY Sponsors.SponsorStatus, Sponsorship.SponsorshipYear
HAVING (((Sponsors.SponsorStatus)=-1) AND ((Sponsorship.SponsorshipYear)=Year(Now())))) AS 5,

(SELECT Count([Table0].HouseholdID) AS FamiliesWithChildren
FROM (SELECT Households_All.HouseholdID, IIf([Children]>0,1,2) AS FamChildren
FROM Households_All
GROUP BY Households_All.HouseholdID, Households_All.Children
HAVING (((IIf([Children]>0,1,2))=1))) as Table0) AS 6,

(SELECT DISTINCT Count(Household.HouseholdID) AS SponsoredFood
FROM Household INNER JOIN Sponsorship ON Household.HouseholdID = Sponsorship.HouseholdID
GROUP BY Household.RegStatus, Sponsorship.SponsorshipYear, Sponsorship.Food
HAVING (((Household.RegStatus)=-1) AND ((Sponsorship.SponsorshipYear)=Year(Now())) AND ((Sponsorship.Food)=-1))) AS 7,

(SELECT DISTINCT Count([Household].[HouseholdID]) AS SponsoredGifts
FROM (Household INNER JOIN Sponsorship ON [Household].[HouseholdID]=[Sponsorship].[HouseholdID]) INNER JOIN (SELECT Households_All.HouseholdID, IIf([Children]>0,1,2) AS FamChildren
FROM Households_All
GROUP BY Households_All.HouseholdID, Households_All.Children
HAVING (((IIf([Children]>0,1,2))=1))) as Table0 ON [Household].[HouseholdID]=[Table0].[HouseholdID]
GROUP BY [Household].[RegStatus], [Sponsorship].[SponsorshipYear], [Sponsorship].[Gifts]
HAVING (((Household.RegStatus)=-1) AND ((Sponsorship.SponsorshipYear)=Year(Now())) AND ((Sponsorship.Gifts)=-1))) as 8,

(SELECT DISTINCT Count(Household.HouseholdID) AS SDSD
FROM Household
GROUP BY Household.RegStatus, Household.SDSD
HAVING (((Household.RegStatus)=-1) AND ((Household.SDSD)=-1))) as 9;


Thanks much,
aldo