Getting Difference between values of 2 tables

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.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200704/1

0
Gman063
4/25/2007 8:06:28 PM
access.forms 6864 articles. 2 followers. Follow

2 Replies
597 Views

Similar Articles

[PageSpeed] 16

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;

-- 
KARL DEWEY
Build a little - Test a little


"Gman063 via AccessMonster.com" wrote:

> 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.
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200704/1
> 
> 
0
Utf
4/25/2007 11:06:00 PM
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 DEWEY wrote:
>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.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200704/1

0
Gman063
4/26/2007 2:40:36 PM
Reply:

Similar Artilces:

Compatibility #2
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) We use MS Office on both PC and Mac in our office and have massive problems opening up Office Documents on the Mac that were put together on PC's, the formatting gets totally messed up, how do I get around this ? In article <59b777c9.-1@webcrossing.caR9absDaxw>, <David_Marriott@officeformac.com> wrote: > We use MS Office on both PC and Mac in our office and have massive problems > opening up Office Documents on the Mac that were put together on PC's, the > formatting gets totally messed up, how do I get a...

How To Create UI Automation Test Suites for VC++ 7.2 Application
Hi All Envoirement -: VC++ 7.2 OS -: Win 2000,XP As i can Develop UI Automation Test Suite For .NET Application Plz Anyone tell me,Can I Develop this Tool for VC++7.2 Application ? If yes ,How ? plz suggest me for it. Regards Tarandeep Singh tarandeep@abosoftware.com Fundamentally, this is hard. There are some commercial programs out there that do this, but getting a test suite that works well, particularly in all resolutions, and is fully scriptable, is a nontrivial problem (well, let's put it this way: I would not accept a fixed-price contract to do this, and wou...

Install Office X on 2 Desktops
I have recently purchased and installed Office X on my iMac (did not register yet) I would like to know if I can also install same on my new G5 and register at that time? I can uninstall from iMac if required. Can I do 2 desktops in lieu of a desktop and a laptop? Unfortunately installing Office X on two desktops is prohibited by the terms of the EULA, and many people have posted here regarding the fact that on many occasions doing this prevents the application from launching. You'll need to uninstall the software from one of the desktop machines. You have no limitations on product regi...

setting up multiple email accounts #2
I followed the rules for setting up the pop3 and smtp accounts for multiple mail accounts, but when I access outlook express the additiona account does not recognize the password I've asked it to remember. HELP! KCB <anonymous@discussions.microsoft.com> wrote: > I followed the rules for setting up the pop3 and smtp > accounts for multiple mail accounts, but when I access > outlook express the additiona account does not recognize > the password I've asked it to remember. HELP! Ask in an Outlook Express newsgroup. -- Brian Tillman ...

Need help getting files from Exchange Server.
We have a computer that had an account on Outlook attached to a Microsoft exchange server. Apparently for the moment, this computer and the server are inextricably tied together. Outlook cannot even be started without logging into the server because information store or whatever is missing. How do you get the information out of the exchange server to wean the computer from it? tom <Spamblocker@ameritech.net> wrote: > We have a computer that had an account on Outlook > attached to a Microsoft exchange server. > > Apparently for the moment, this computer and the > se...

How to get TASK_ID field for summary tasks without using Project.a
I know for tasks which are not summary tasks we can get TASK_ID field using statusing web service. But i could nto find any other options than Project web service to get TASK_ID field for summary tasks and the top level project task. Problem of using Project web service is that in my custom sharedpoint web part where we are using PSI web services we get all the data required using Resource and Statusing web service for the logged in resource. But Statusing web service retrieves TASK_ID only for actual tasks and not for summary tasks. Now just to get TASK_ID of summary tas...

Can't get the proper display of a field in my report.
I have 2 tables, both using autonumbers for their primary key. The first table is for contacts (i.e. last name, first name, etc.). The second table is for businesses (business name, etc.) I have a field in my contacts table that has a number format so it can be used as a foreign key for the business table. I then set up the relationship between them & enforced referential integrity. When I run a query, I see the name of the business (after setting up a combo box) - no problem. When I run a report based on that query, a number is displayed (not the business name). Suggestions, pleas...

pivot table %
I have 2 columns in a pivot table - decription and amount. I need to calc a % of each value of the total. I don't know how to do that. ...

String Table (VC6 IDE)
I have strings in English language in the "String Table" of my project (myProject.rc). I'm loading them using: CString msg; msg.LoadString(150); Now, I need to internationalise my app. How can I do that? How can I add support for multiple languages? Which is the best way to do that? >I have strings in English language in the "String Table" of my project >(myProject.rc). I'm loading them using: > >CString msg; >msg.LoadString(150); > >Now, I need to internationalise my app. How can I do that? How can I add >support for multiple lang...

Line Graph with two different data points
Hello, I am trying to create a line chart with numbers from my product Vs a competitor. The problem i am having is how can i make a chart that will have four data points comparing to each other? For example Product A- 1.5 1.2 1.6 1.8 Product B- 2.0 1.1 1.2 1.3 Product A- 70% 20 % 30% 67% Product B- 65% 30% 65% 55% How do i get all of these numbers on one chart? Is a line chart correct. I want to see these numbers compared?? Thank you so much Hi, Since the number don't appear related to the percentages you might plot them on the same chart but two different axes. If they were relate...

Query cross two table
Hi, I have two tables, tbAdmission and tbCode. In my tbAdmission, I have Code1, Code2 and Code3. In my tbCode, I have Code, Description1 and Description2. In my Form, frmAdmission, I have txtCode1, txtCode2 and txtCode3 that are all bounded to tbAdmission. And txtDescription1Code1, txtDescription2Code1, txtDescription1Code2, txtDescription2Code2, txtDescription1Code3 and txtDescription2Code3 that are unbounded and only for displaying the descriptions. txtCode1, txtCode2 and txtCode3 all refer to Code in tbCode to retrieve Description1 and Description2 for displaying in the unbou...

Conversion Errors Table
Hello, I'm new to working with Access, I just converted an Access 97 databas into Access 2002. It tells me there were errors, and to look at th Conversion Errors Table. But nowhere in the message or in the MS Hel is there anything telling me where to find this table. Can someon help? Thanks Patric -- psha ----------------------------------------------------------------------- pshaw's Profile: http://www.officehelp.in/member.php?userid=493 View this thread: http://www.officehelp.in/showthread.php?t=125029 Posted from - http://www.officehelp.i I'd expect to find it in the new...

Transferring Field from Existing Table/limitations and change of d
Thank you in advance for your help! I have two Excel spreadsheets that I successfully imported into Access 2003 and created tables for. I need to add the field from one table to the other, but there is not a direct match in the relationship. The large table uses the Employee ID as the primary key. The smaller table contains one field that lists a subset of these Employee ID numbers (a selection of certain employees). I need to transfer this field to the larger table, but I do not know how to tell Access to match up the corresponding numbers (i.e., the large table lists all employees, bu...

Controlling printed records when report bound to multiple tables
I created a report that uses the control from a form to generate a report based on that record's primary key. This form also has a subform which has relationships tied to the primary key for record identification and is linked to the main table. When preview the report the data from the subform either does not show up in the preview when using the filter [control]=[form]![control].[value] or makes multiple copies of the report equal to the number of entries in the subform's table. Is there any way around this? I have tried queries but have not found a way to use a f...

Formula so that response is >value
The same cell has two formulas, IF(D3>0,LOG(C3/F3),IF(D3<1,LOG(C3/10)). For example, if C3 is 1.0E+06, D3 is 100, then F3 will be 1.0E+03 (due to another formula), and the response for the first part should read 3.0. If D3 is 0, F3 will be <10 (due to another formula), and the response should read >5.0, not simply 5.0. I'm not sure I follow your formulas as described, but if you're looking to have your results display the ">" character along with the value, then maybe this example will lead you in the right direction: Let's say cell A1 contains ...

how to compare 2 values in a report (Invoice Total vs Payments)
while running a report how would I set a message "out of balance" if my invoice amount (Table 1 ) does not equal the value of my total payments (table 2). If the values were the same then no message would be printed. Thank You Create a new command button to check the report before you print it. You will have to enter code for the button along the lines of: If [Invoice Total] <> [Payments] Then MsgBox "This account is out of balance" Endif "Rita" wrote: > while running a report how would I set a message "out of balance" if > my invo...

setting a value to a field for all records
I need to create 'Select All' and 'Unselect All' buttons to put true or false in a yes/no field. Anyone have an idea for the easiest way to iterate through all the records? ...

Obtaining Max and Min Values with Sumproduct
Is this possible? I have a data set of 4,000 lines and want to extract the maximum an minimum values for Criteria3, where the conditions for Criteria1 an Criteria2 are met. I would be grateful for any assistance t -- Timmy Mac ----------------------------------------------------------------------- Timmy Mac1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1518 View this thread: http://www.excelforum.com/showthread.php?threadid=47789 =MIN(IF((A1:A10="a")*(B1:B10="b"),C1:C10)) entered with ctrl + shift & enter do the same for MAX -- ...

Password protected #2
i have a spreadsheet is protected and requires a password...problem is dont know the password and i need to edit the sheet! Anyone got any tips.... -- Message posted from http://www.ExcelForum.com Crack it by installing this free utility at: http://www.straxx.com/excel/password.html HTH Jason Atlanta, GA >-----Original Message----- >i have a spreadsheet is protected and requires a password...problem is i >dont know the password and i need to edit the sheet! > >Anyone got any tips....? > > >--- >Message posted from http://www.ExcelForum.com/ > >. > ...

Outlook Security Settings #2
I am trying to use Outlook security settings. I have installed the settings precisely as described on the MS site and the settings work perfectly for me and one or two others but not for the rest of my defined group. We are using Exchange 2003, Outlook 2003 and Outlook XP. I cannot find any debugging or logging info anywhere and am a little stuck as to what to do next. Can anyone offer any ideas??? jON ...

Need disk # 2 for Home Publishing Premium 2000
The #2 disk of a (6) disk set in my Home Publishing Premium 2000 was demolished in my CD drive. This is the setup disk and since the CD is no longer available from Microsoft I need a copy badly. Is there anyone that can successfully copy a #2 disk? I will be more than happy to pay anyone for there trouble. Patsy wrote: > The #2 disk of a (6) disk set in my Home Publishing > Premium 2000 was demolished in my CD drive. This is the > setup disk and since the CD is no longer available from > Microsoft I need a copy badly. Is there anyone that can > successfully copy a #2 dis...

if value not found in table ?
Hello all I need to display a msgbox if a value is not found in a table. Something like: If value not_in table.field then msgbix end if I know that code wont work is just an ilustration of what i am looking for Thanks in advance Use DLookup() to see if the value is in the table. If it's not found, the result will be Null. So, use IsNull() to test the result. Here's how to get your Dlookup() expression working: Getting a value from a table: DLookup() at: http://allenbrowne.com/casu-07.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access us...

How to get the handle of a Help file belonging to other process.
Hi all, In my application,I want to change the Index of a HTMLHELP file which belongs to a dialog running in other exe.In order to do this I need to find whether the Help file is open or not & get the handle of that to use HH_DISPLAY_INDEX .Is there any way to do this?? Note: I dont want to use FindWindow() as it is giving rise to some problems in my application.. Thanks in advance ...

Linked Table Manager Doesn't Work in Access 2003
I recently upgraded to Access 2003 and have found that the menu option: Tools/Database Utilities/Linked Table Manager no longer works correctly for re-linking my front end database to the backend. No tables show in the table list. If I click “Select All” and then fill in the path to the backend database I get the following error message: Method ‘List’ of object ‘IfieldListWnd’ failed. As a work around I have to delete all the table links from the front-end and then File/Get External Data/Link Tables. Is anybody else having this problem? Thanks in advance for your help. "...

Help on adding values to a bar chart
Hello, Here is my problem. I currently have a bar chart that has 8 X-axis bars. I need to add 2 more to the X-axis. The labels for the x-axis are based on cells within another spreadsheet, but are not consecutive cells, they are spread apart. So when I go to add the two more cells, I can only add one. When I click on the second cell to add it to the list, all of the previous cells get unselected. I have even tried to manually add these two values in. Is there a limit to how many values can be on the X-axis? Please Help!!! Thanks. To select non-consecutive cells: select first cell, h...