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;