SELECT filling in values it shouldn't
Posted: Fri Sep 27, 2002 10:04 pm
This is my SELECT statement:
The purpose of this query is to show my online marketing expenses and revenue for the 23 account categories in the Accounts table. The point being that I can get some feeling of what my ROI is. However, the query returns the wrong sum values although it does return exactly 23 rows. It seems that if there is a value in more than one column (other than AccountName column) it doubles up the addition.
Say I had an account "Printers", with $100 in Google Expenses and $200 in revenue in my data tables. It would show $200 in Google Expense and $400 in revenue in my query results. Why is this?
If I haven't made myself clear, please ask for clarification! THanks,
Caleb, at his wits' end.
Code: Select all
SELECT Accounts.AccountName, Sum(MEtable.MEGoogle) AS Google, Sum(MEtable.MEOverture) AS Overture, Sum(SOtable.SaleAmount) AS SumOfSaleAmount
FROM (Accounts LEFT JOIN MEtable ON Accounts.AccountCode = MEtable.MEAccountID) LEFT JOIN SOtable ON Accounts.AccountCode = SOtable.AccountCode
GROUP BY Accounts.AccountName;Say I had an account "Printers", with $100 in Google Expenses and $200 in revenue in my data tables. It would show $200 in Google Expense and $400 in revenue in my query results. Why is this?
If I haven't made myself clear, please ask for clarification! THanks,
Caleb, at his wits' end.