Multiple Table Query using Sum Function

[Scott Cannon]

I am trying to query 3 tables all related by Clinet_ID. The Clients
table, Monthly_Expenses table and Monthly_Income table. Each client
can have 0>M instances of expenses, past due expenses, and income so I
am using the SUM aggregate function to get the totals for the
Monthly_Epenses.Amount, Monthly_Expenses.Past_Due_Amount, and
Monthly_Income.Amount.

Then problem I am having and cannot seem to resolve is that the
Monthly_Income is not toalling correctly. The Monthly_Expenses.Amount
calculates fine as well as the Past_Due_Amount. The income is coming
out at some multiple of the actual amount. Client_ID 9 has
Monthly_Income.Amount of $500 and the query result is $4000.

Should I do multiple separate queries and then query the results table
to get the Monthly Income snapshot I am looking for?

Here's the SQL for the query as MS Access built it:

SELECT Clients.Client_ID, Sum(Monthly_Income.Amount) AS SumOfAmount,
Sum(Monthly_Expenses.Amount) AS SumOfAmount1,
Sum(Monthly_Expenses.Past_Due_Amount) AS SumOfPast_Due_Amount
FROM (Clients INNER JOIN Monthly_Expenses ON Clients.Client_ID =
Monthly_Expenses.Client_ID) INNER JOIN Monthly_Income ON
Clients.Client_ID = Monthly_Income.Client_ID
GROUP BY Clients.Client_ID;
Here's one I wrote myself that is aliased for readablility and returns
the same results and the MS Access query:

SELECT C.Client_ID, Sum(MI.Amount) AS [Monthly Income], Sum(ME.Amount)
AS [Monthly Expenses], sum(ME.Past_Due_Amount) AS Past_Due_Amount,
(Sum(MI.Amount)-Sum(ME.Amount)) AS [Monthly Net Income]
FROM Clients AS C, Monthly_Income AS MI, Monthly_Expenses AS ME
WHERE C.Client_ID = MI.Client_ID AND C.Client_ID = ME.Client_ID
GROUP BY C.Client_ID;

[Christoph Sticksel]

Scott Cannon <sm********@hotmail.com> wrote:

I am trying to query 3 tables all related by Clinet_ID. The Clients
table, Monthly_Expenses table and Monthly_Income table. Each client
can have 0>M instances of expenses, past due expenses, and income so I
am using the SUM aggregate function to get the totals for the
Monthly_Epenses.Amount, Monthly_Expenses.Past_Due_Amount, and
Monthly_Income.Amount. Then problem I am having and cannot seem to resolve is that the
Monthly_Income is not toalling correctly. The Monthly_Expenses.Amount
calculates fine as well as the Past_Due_Amount. The income is coming
out at some multiple of the actual amount. Client_ID 9 has
Monthly_Income.Amount of $500 and the query result is $4000. Should I do multiple separate queries and then query the results table
to get the Monthly Income snapshot I am looking for?

Yes, you should. The joins are performed first, then the sum function is
run over all grouped records. In your case some fields are summed
several times. Remove the GROUP BY clause and look at the ungrouped
query to see what I mean.

If there are three tables tblA, tblB and tblC joined on ID like this

tblA:

ID
-
1
2

tblB:

ID|Expense
1|1
tblC:

ID|Income
1|1
1|2
A query would return this

ID|Expense|Income
1| 1| 1
1| 1| 2

If you sum grouped by ID, you'd get

ID|Expense|Income
1| 2| 3

which is wrong for Expense.

Sum the two tables separetely and then join the queries to your main
table.

HTH,
Chris

[Scott Cannon]

Chris,

That did the trick. I ended up summing the Income and Expense data in
their own separate queries, then used them along with the original
tables to create the financial snapshot I was looking for with only
the Net Income: field as a calculated field using the Income and
Expense query totals in the expression.

Using the Nz function setting any nulls to zero also helped to make a
perfect query result set.

Thanks.

Scott