Results 1 to 2 of 2

Thread: Fairly Simple Query

  1. #1
    Join Date
    Sep 2010
    Posts
    1

    Fairly Simple Query

    Im running the following query to a MS ACCESS 2007 Database:

    "SELECT CustomerID, SUM(AmountToPay) As AmountOwing, SUM(AmountPaid) As PaidOff FROM Orders, Payment WHERE Orders.CustomerID = Payment.CustID AND CustomerID = 20202 GROUP BY CustomerID;"

    I dont get the right amounts for AmountOwing, and PaidOff, which are the SUMs. The answer is DOUBLE what it should be for both and I cant work out why. Thanks for any help.

  2. #2
    Join Date
    Apr 2009
    Posts
    86
    Calvin1991, Without being able to see your data, I can only make a guess. The clue I am basing this on is the amounts are exactly doubled.

    If CustomerId is not Unique, the Join could generate extra 'duplicate' rows. For example:
    Code:
    Table Orders
    
    Customerid orderId
    20202      1
    20202      2
    
    Table Payment
    
    OrderID CustId AmountToPay   AmountPaid
    1       20202  100.00         90.00
    1       20202  100.00         30.00
    Running your query with this data you would bet
    Code:
    20202  100.00  90.00
    20202  100.00  30.00
    20202  100.00  90.00
    20202  100.00  30.00
    When summarized, it would be:
    Code:
    20202 400.00  240.00
    Instead of only having a summary:
    Code:
    20202 200.00  120.00
    Try removing the Sum functions and Group by and examine the data that is being returned so you can see exactly what is being summarized.
    Code:
    SELECT CustomerID
         , AmountToPay
         , AmountPaid
    FROM Orders
       , Payment 
    WHERE Orders.CustomerID = Payment.CustID 
      AND CustomerID        = 20202

Posting Permissions

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