Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2013
    Posts
    3

    Unanswered: if null statement

    I am have problems with IIf(IsNull(Sum(CountLitterPuppies.LitterPuppies)), 0, Sum(CountLitterPuppies.LitterPuppies)) AS TotalPuppies. I need it to return 0 for dogs with no puppies.

    SELECT Bulldog.dog_ID, Bulldog.Gender, Bulldog.Breed, Bulldog.Color, Bulldog.Reg_ID, Bulldog.Price, Bulldog.Bdate, Bulldog.LitID, Bulldog.PhNum, IIf(IsNull(Sum(CountLitterPuppies.LitterPuppies)), 0, Sum(CountLitterPuppies.LitterPuppies)) AS TotalPuppies
    FROM Bulldog AS Bulldog_1 INNER JOIN (Bulldog INNER JOIN CountLitterPuppies ON Bulldog.dog_ID = CountLitterPuppies.d_ID) ON Bulldog_1.dog_ID = CountLitterPuppies.s_ID
    GROUP BY Bulldog.dog_ID, Bulldog.Gender, Bulldog.Breed, Bulldog.Color, Bulldog.Reg_ID, Bulldog.Price, Bulldog.Bdate, Bulldog.LitID, Bulldog.PhNum
    HAVING (((Bulldog.dog_ID)=[Dog ID?]));

    UNION

    SELECT Bulldog_1.dog_ID, Bulldog_1.Gender, Bulldog_1.Breed, Bulldog_1.Color, Bulldog_1.Reg_ID, Bulldog_1.Price, Bulldog_1.Bdate, Bulldog_1.LitID, Bulldog_1.PhNum, IIf(IsNull(Sum(CountLitterPuppies.LitterPuppies)), 0, Sum(CountLitterPuppies.LitterPuppies)) AS TotalPuppies
    FROM Bulldog AS Bulldog_1 INNER JOIN (Bulldog INNER JOIN CountLitterPuppies ON Bulldog.dog_ID = CountLitterPuppies.d_ID) ON Bulldog_1.dog_ID = CountLitterPuppies.s_ID
    GROUP BY Bulldog_1.dog_ID, Bulldog_1.Gender, Bulldog_1.Breed, Bulldog_1.Color, Bulldog_1.Reg_ID, Bulldog_1.Price, Bulldog_1.Bdate, Bulldog_1.LitID, Bulldog_1.PhNum
    HAVING (((Bulldog_1.dog_ID)=[Dog ID?]));

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Sum() doesn't return null. You need to change the order of the functions:
    Code:
    Sum(IIf(IsNull(CountLitterPuppies.LitterPuppies), 0, CountLitterPuppies.LitterPuppies))
    Give this a try.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Apr 2013
    Posts
    3
    I changed it around. I am getting a sum for bulldogs with puppies, but am still not getting a zero for bulldogs without puppies.

    SELECT Bulldog.dog_ID, Bulldog.Gender, Bulldog.Breed, Bulldog.Color, Bulldog.Reg_ID, Bulldog.Price, Bulldog.Bdate, Bulldog.LitID, Bulldog.PhNum, Sum(IIf(IsNull(CountLitterPuppies.LitterPuppies), 0, CountLitterPuppies.LitterPuppies)) AS TotalPuppies
    FROM Bulldog AS Bulldog_1 INNER JOIN (Bulldog INNER JOIN CountLitterPuppies ON Bulldog.dog_ID = CountLitterPuppies.d_ID) ON Bulldog_1.dog_ID = CountLitterPuppies.s_ID
    GROUP BY Bulldog.dog_ID, Bulldog.Gender, Bulldog.Breed, Bulldog.Color, Bulldog.Reg_ID, Bulldog.Price, Bulldog.Bdate, Bulldog.LitID, Bulldog.PhNum
    HAVING (((Bulldog.dog_ID)=[Dog ID?]));

    UNION

    SELECT Bulldog_1.dog_ID, Bulldog_1.Gender, Bulldog_1.Breed, Bulldog_1.Color, Bulldog_1.Reg_ID, Bulldog_1.Price, Bulldog_1.Bdate, Bulldog_1.LitID, Bulldog_1.PhNum,Sum(IIf(IsNull(CountLitterPuppies. LitterPuppies), 0, CountLitterPuppies.LitterPuppies)) AS TotalPuppies
    FROM Bulldog AS Bulldog_1 INNER JOIN (Bulldog INNER JOIN CountLitterPuppies ON Bulldog.dog_ID = CountLitterPuppies.d_ID) ON Bulldog_1.dog_ID = CountLitterPuppies.s_ID
    GROUP BY Bulldog_1.dog_ID, Bulldog_1.Gender, Bulldog_1.Breed, Bulldog_1.Color, Bulldog_1.Reg_ID, Bulldog_1.Price, Bulldog_1.Bdate, Bulldog_1.LitID, Bulldog_1.PhNum
    HAVING (((Bulldog_1.dog_ID)=[Dog ID?]));

  4. #4
    Join Date
    Apr 2013
    Posts
    3
    This works, but I wanted to make it shorter and more efficient.

    SELECT Bulldog.dog_ID, Bulldog.Gender, Bulldog.Breed, Bulldog.Color, Bulldog.Reg_ID, Bulldog.Price, Bulldog.Bdate, Bulldog.LitID, Bulldog.PhNum, Sum(CountLitterPuppies.LitterPuppies) AS TotalPuppies
    FROM Bulldog AS Bulldog_1 INNER JOIN (Bulldog INNER JOIN CountLitterPuppies ON Bulldog.dog_ID = CountLitterPuppies.d_ID) ON Bulldog_1.dog_ID = CountLitterPuppies.s_ID
    GROUP BY Bulldog.dog_ID, Bulldog.Gender, Bulldog.Breed, Bulldog.Color, Bulldog.Reg_ID, Bulldog.Price, Bulldog.Bdate, Bulldog.LitID, Bulldog.PhNum
    HAVING (((Bulldog.dog_ID)=[Dog ID?]));

    UNION

    SELECT Bulldog_1.dog_ID, Bulldog_1.Gender, Bulldog_1.Breed, Bulldog_1.Color, Bulldog_1.Reg_ID, Bulldog_1.Price, Bulldog_1.Bdate, Bulldog_1.LitID, Bulldog_1.PhNum, Sum(CountLitterPuppies.LitterPuppies) AS TotalPuppies
    FROM Bulldog AS Bulldog_1 INNER JOIN (Bulldog INNER JOIN CountLitterPuppies ON Bulldog.dog_ID = CountLitterPuppies.d_ID) ON Bulldog_1.dog_ID = CountLitterPuppies.s_ID
    GROUP BY Bulldog_1.dog_ID, Bulldog_1.Gender, Bulldog_1.Breed, Bulldog_1.Color, Bulldog_1.Reg_ID, Bulldog_1.Price, Bulldog_1.Bdate, Bulldog_1.LitID, Bulldog_1.PhNum
    HAVING (((Bulldog_1.dog_ID)=[Dog ID?]));

    UNION

    SELECT Bulldog.dog_ID, Bulldog.Gender, Bulldog.Breed, Bulldog.Color, Bulldog.Reg_ID, Bulldog.Price, Bulldog.Bdate, Bulldog.LitID, Bulldog.PhNum, 0 AS TotalPuppies
    FROM Bulldog LEFT JOIN CountLitterPuppies ON Bulldog.[dog_ID] = CountLitterPuppies.[d_ID]
    WHERE (((Bulldog.Gender)="Female") AND ((CountLitterPuppies.d_ID) Is Null))
    GROUP BY Bulldog.dog_ID, Bulldog.Gender, Bulldog.Breed, Bulldog.Color, Bulldog.Reg_ID, Bulldog.Price, Bulldog.Bdate, Bulldog.LitID, Bulldog.PhNum
    HAVING (((Bulldog.dog_ID)=[Dog ID?]));

    UNION

    SELECT Bulldog.dog_ID, Bulldog.Gender, Bulldog.Breed, Bulldog.Color, Bulldog.Reg_ID, Bulldog.Price, Bulldog.Bdate, Bulldog.LitID, Bulldog.PhNum, 0 AS TotalPuppies
    FROM Bulldog LEFT JOIN CountLitterPuppies ON Bulldog.[dog_ID] = CountLitterPuppies.[s_ID]
    WHERE (((Bulldog.Gender)="Male") AND ((CountLitterPuppies.s_ID) Is Null))
    GROUP BY Bulldog.dog_ID, Bulldog.Gender, Bulldog.Breed, Bulldog.Color, Bulldog.Reg_ID, Bulldog.Price, Bulldog.Bdate, Bulldog.LitID, Bulldog.PhNum
    HAVING (((Bulldog.dog_ID)=[Dog ID?]))
    ORDER BY dog_ID;

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Looking at the Union query that works, I suspect your problem is that you have records in Bulldog that don't have corresponding records in CountLitterPuppies. This is one of those questions that really requires a sample dataset so that us total strangers get a better feel for the situation.
    ***ETA***
    You need to investigate outer join (LEFT JOIN and RIGHT JOIN), but without seeing how your database is structured, I can't offer more advice than that.
    Last edited by weejas; 04-22-13 at 11:14. Reason: Misread the query
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •