Getting Difference between values of 2 tables

  • Thread starter Thread starter Gman063 via AccessMonster.com
  • Start date Start date
G

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.
 
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;
 
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

KARL said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Back
Top