Update column of one table reference to two column of another table in access 2007

[joshynaresh]

Suppose I have two table.


1. LimitTable

AccNo	Name	OutstandingBal
071	Naresh
072	Ranjan

2. TxnTable

AccNo	Debit	Credit
071	5000	3000
072	3000	2000
071	10000	8000
072	5000	3000

I Want to Update LimitTable as
3. LimitTable

AccNo	Name	OutstandingBal
071	Naresh	4000
072	Ranjan	3000

Note: (Debit-Credit) of same account

Is it possible in access 2007?
Please help in detail with query/code to me how can I do. Thanking You in advance.

[alansidman]

Why update the table? Just create a select query. The information is always there.

SELECT LimitTable.AccNo, LimitTable.Name, Sum([Debit]-[Credit]) AS OutstandingBal
FROM LimitTable INNER JOIN [Txn Table] ON LimitTable.AccNo = [Txn Table].AccNo
GROUP BY LimitTable.AccNo, LimitTable.Name;

Calculations should not be stored in tables in a RDBMS. Calculations are performed in queries and run as necessary.

[joshynaresh]

it works in above table. but when i changed data in [Txn Table] as

ID	AccNo	Debit	Credit
7	071	5,000.00
8	072	3,000.00
9	071	10,000.00
10	072	5,000.00
13	071		3,000.00
14	071		8,000.00
15	072		2,000.00
16	072		3,000.00

it returns null value. and I also watn to store in [LimitTable].Outstanding_Bal. Plz Plz help me. Thank You

[joshynaresh]

i have made mistake. i inserted blank field. when i filled 0 in blank field it woks. but i want to store too.
I have another question releted with this table about form. is it Possible. Thank You.

[alansidman]

and I also watn to store in [LimitTable].Outstanding_Bal.

This is not a proper way to run a relational database. It is a spreadsheet mindset. When you store calculations in tables, you de-normalize the data. Every time a field changes value, you have to go back and update each of the calculations. As I said earlier, this is something that needs to be done in a query.

As to the query, you need to wrap the expression in NZ functions.

SELECT LimitTable.AccNo, LimitTable.Name, Sum(NZ([Debit],0)-NZ([Credit],0)) AS OutstandingBal
FROM LimitTable INNER JOIN [Txn Table] ON LimitTable.AccNo = [Txn Table].AccNo
GROUP BY LimitTable.AccNo, LimitTable.Name;

Look at this link on the NZ function http://www.techonthenet.com/access/f...dvanced/nz.php

Look at this link on why calculations should not be stored. http://allenbrowne.com/casu-14.html and here http://www.utteraccess.com/wiki/inde...d_Audit_Trails

Here is how you would create a calculation in a form in an unbound control

http://office.microsoft.com/en-us/ac...010108354.aspx


This might be a good read also

htp://forums.aspfree.com/microsoft-access-help-18/access-vs-excel-349267.html