Getting Due Accounts

Databases
1

I’m really having a hard time in getting the due accounts of a certain customer in MS Access. I have three tables - customerinfo, bills, payments. In my bills tables, a certain customer already had three bills and the customer only made a single payment. The total cost of the bills is worth 1500.00 whereas, in the payment the customer only pay 500.00. If I make use of the SUM function in my query I’ll get a sum of 1500 from payment table.


SELECT customerinfo.id, customerinfo.firstname, customerinfo.middlename, customerinfo.lastname, Sum(bills.amount) AS SumOfamount, Sum(payment.amount) AS SumOfamount1
FROM (customerinfo INNER JOIN bills ON customerinfo.id = bills.custid) INNER JOIN payment ON bills.custid = payment.custid
GROUP BY customerinfo.id, customerinfo.firstname, customerinfo.middlename, customerinfo.lastname;
2

You should not join the bills and payment tables


select customerinfo.id, 
       customerinfo.firstname, 
       customerinfo.middlename, 
       customerinfo.lastname, 
       (select sum(amount)
          from bills
         where customerinfo.id = bills.custid) as SumOfamount, 
       (select sum(amount)
           from payment
         where customerinfo.id = payment.custid) as SumOfamount1
  from customerinfo
3

Thank you so much swampBoogie.