Access database returns double SUM for field Income

I have the following query in access 2003 mdb.

SELECT Company.Name, Company.Address, Place.Name_of_Place, Sum(Income.Value) AS Income, Sum(Invoice.Value) AS Invoice 
FROM ((Company LEFT JOIN Invoice ON Company.CompanyID = Invoice.CompanyID) LEFT JOIN Income ON Company.CompanyID = Income.CompanyID) INNER JOIN Place ON 
Place.Postal = Company.Postal 
GROUP BY Company.Name, Company.Address, Place.Name_of_Place, Company.CompanyID
HAVING ((iif(IsNull(Sum(Invoice.Value)), 0, Sum(Invoice.Value)) - iif(IsNull(Sum(Income.Value)), 0, Sum(Income.Value))) > 0) 
ORDER BY Company.Name;

Income field value is 500, but query returns 1000. There must be something with those Left joins that this Income table is twice searched. How to solve this? I'm thinking to in my program do simple division by 2 for this column, but I'd rat开发者_C百科her like to solve this on database level.

Regards,

Vajda

When you join the Company with the Invoice, the result will have as many rows as the rows in Invoice. So if a company has 2 invoices, you will have 2 rows.

Then, when you join that with the Income (which I am not sure how many rows per company it has) the result will be 2 rows for each row of Income.

You will have to resort to sub-queries, like this:

SELECT
  Company.Name,
  Company.Address,
  Place.Name_of_Place,
  (SELECT SUM(Income.Value) FROM Income WHERE Income.CompanyID=Company.CompanyID) AS Income,
  (SELECT SUM(Invoice.Value) FROM Invoice WHERE Invoice.CompanyID=Company.CompanyID) AS Invoice
FROM
  Company INNER JOIN Place ON Place.Postal = Company.Postal
WHERE 
  Invoice - Income > 0
ORDER BY
  Company.Name;