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.

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

---