Getting Difference between values of 2 tables

[Gman063 via AccessMonster.com]

Info:
I have the following table that stores the total amount
available for a particular Document:

tblTotalAvail:
ID CLIN Amount
1 0001 10,000
2 0002 30,000
3 0003 25,000
Etc... You get the idea.

I have the following table that stores the expenditure for
for the same document:

tblExpenditures:
ID DateFunded CLIN Amount
1 21-Jan-07 0001 2,500
2 30-Jan-07 0001 3,700
3 03-Feb-07 0002 11,000
4 14-Feb-07 0001 1,200
5 17-Feb-07 0002 7,500
Etc....


Here is what I am trying to do....I want to Sum the amount
by CLIN in tblexpenditures, take that summary and subtract it
from the amount in tblTotalAvail with same CLIN and display it
in a List Box.

Example of the list box display when complete:

"Amount Remaining"

CLIN Amount Remaining
0001 2,600
0002 11,500
0003 25,000


Is this possible, and if so any help or ideas you can give would
be very much appreciated.

 

[Guest]

Try this --
SELECT tblTotalAvail.CLIN, Sum(tblTotalAvail.Amount) AS Budget,
Sum(tblExpenditures.Amount) AS Expenses,
Sum(tblTotalAvail.Amount)-Sum(tblExpenditures.Amount) AS [Amount Remaining]
FROM tblTotalAvail LEFT JOIN tblExpenditures ON tblTotalAvail.CLIN =
tblExpenditures.CLIN
GROUP BY tblTotalAvail.CLIN;

 

[Gman063 via AccessMonster.com]

Thanks for the help, your reply did not do what I needed, however it did
spark the mental juices to come up with a solution that will. I really do
appriciate your help. Got me looking at it in a different way that sparked
new ideas.

Thanks again

Try this --
SELECT tblTotalAvail.CLIN, Sum(tblTotalAvail.Amount) AS Budget,
Sum(tblExpenditures.Amount) AS Expenses,
Sum(tblTotalAvail.Amount)-Sum(tblExpenditures.Amount) AS [Amount Remaining]
FROM tblTotalAvail LEFT JOIN tblExpenditures ON tblTotalAvail.CLIN =
tblExpenditures.CLIN
GROUP BY tblTotalAvail.CLIN;

Info:
I have the following table that stores the total amount

[quoted text clipped - 35 lines]

Is this possible, and if so any help or ideas you can give would
be very much appreciated.