Multiple Table Query using Sum Function

[Scott Cannon]

I am trying to query 3 tables all related by Clinet_ID. The Clients
table, Monthly_Expense s 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_Expense s.Past_Due_Amou nt, 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_Expense s.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_Inc ome.Amount) AS SumOfAmount,
Sum(Monthly_Exp enses.Amount) AS SumOfAmount1,
Sum(Monthly_Exp enses.Past_Due_ Amount) AS SumOfPast_Due_A mount
FROM (Clients INNER JOIN Monthly_Expense s ON Clients.Client_ ID =
Monthly_Expense s.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_Expense s 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********@hot mail.com> wrote:

I am trying to query 3 tables all related by Clinet_ID. The Clients
table, Monthly_Expense s 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_Expense s.Past_Due_Amou nt, 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_Expense s.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|Inco me
1| 1| 1
1| 1| 2

If you sum grouped by ID, you'd get

ID|Expense|Inco me
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