SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting Due Accounts

    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.
    Code:
    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. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    695
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    You should not join the bills and payment tables

    Code:
    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. #3
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you so much swampBoogie.


Bookmarks

Posting Permissions

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