Access Database Total Field not calculates empty records?

Question

My Query Result is as below

Query:

SELECT reservations.customerid, 
 (SELECT SUM(balances.balance) FROM balances 
  WHERE balances.customer_id = reservations.customerid) 
                               AS Preveious_balance ,
 (SELECT SUM(services.Amount_due) 
   FROM services 
   WHERE services.customer_id = reservations.customerid 
     AND services.status=0) AS Service_due ,  
(SELECT SUM(foods.Amount_due) 
    FROM foods WHERE foods.customer_id = reservations.customerid 
    AND foods.status=0) AS Food_due,
((due_nights.Due_nights - reservations.billed_nights) * rooms.rate) 
  as Accomendation,
(Preveious_balance + Service_due  +  Food_due + Accomendation) 
    AS Total_due 

FROM reservations, due_nights, rooms 
  WHERE reservations.customerid = due_nights.customerid 
 AND reservations.roomno = rooms.roomno

Total_due is Empty if some other columns value gets empty so how to fix this ?

Answer 1

SELECT reservations.customerid, 
     (SELECT SUM (balances.balance)
         FROM balances WHERE balances.customer_id = reservations.customerid)
           AS Preveious_balance ,
     (SELECT SUM(services.Amount_due)
            FROM services WHERE services.customer_id = reservations.customerid 
             AND services.status=0) AS Service_due ,  
    (SELECT SUM(foods.Amount_due)
            FROM foods WHERE foods.customer_id = reservations.customerid 
           AND foods.status=0) AS Food_due,
    ((due_nights.Due_nights - reservations.billed_nights) * rooms.rate)
          as Accomendation,
    (NZ(Preveious_balance,0) + NZ(Service_due,0)  +  NZ(Food_due,0) + NZ(Accomendation,0)) AS Total_due 
  FROM reservations, due_nights, rooms 
WHERE reservations.customerid = due_nights.customerid 
AND reservations.roomno = rooms.roomno

You can you NZ function of Access to check whether the sum is null.If so assign 0. It will solve your issue

If not access

SELECT reservations.customerid, 
 (SELECT IIf(Sum(balances.balance) Is Null, 0, Sum(balances.balance))
     FROM balances WHERE balances.customer_id = reservations.customerid)
       AS Preveious_balance ,
 (SELECT IIf(Sum(services.Amount_due) Is Null, 0, Sum(services.Amount_due))
        FROM services WHERE services.customer_id = reservations.customerid 
         AND services.status=0) AS Service_due ,  

 (SELECT IIf(Sum(foods.Amount_due) Is Null, 0, Sum(foods.Amount_due))
        FROM foods WHERE foods.customer_id = reservations.customerid 
       AND foods.status=0) AS Food_due,
((due_nights.Due_nights - reservations.billed_nights) * rooms.rate)
      as Accomendation,
(Preveious_balance + Service_due  +  Food_due + Accomendation) AS Total_due 
   FROM reservations, due_nights, rooms 
 WHERE reservations.customerid = due_nights.customerid 
 AND reservations.roomno = rooms.roomno

Answer 2

SELECT reservations.customerid, 
 (SELECT IIf(Sum(balances.balance) Is Null, 0, Sum(balances.balance))
     FROM balances WHERE balances.customer_id = reservations.customerid)
       AS Preveious_balance ,
 (SELECT IIf(Sum(services.Amount_due) Is Null, 0, Sum(services.Amount_due))
        FROM services WHERE services.customer_id = reservations.customerid 
         AND services.status=0) AS Service_due ,  

 (SELECT IIf(Sum(foods.Amount_due) Is Null, 0, Sum(foods.Amount_due))
        FROM foods WHERE foods.customer_id = reservations.customerid 
       AND foods.status=0) AS Food_due,
((due_nights.Due_nights - reservations.billed_nights) * rooms.rate)
      as Accomendation,
(Preveious_balance + Service_due  +  Food_due + Accomendation) AS Total_due 
   FROM reservations, due_nights, rooms 
 WHERE reservations.customerid = due_nights.customerid 
 AND reservations.roomno = rooms.roomno

if you are using ADO.NET or other programing to check null values this is correct becouse Nz is only available in MS access.