By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,529 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

How to update table with query in simple accounting

yosiro
P: 34
I can write this function easily in excel but very hard in access.

Some table like this:
_____________________________________
ID | IN | OUT | SALDO |
-------------------------------------
1 | 1000 | 0 | 1000 |
2 | 1000 | 0 | 2000 |
3 | 0 | 500 | 1500 |
4 | 3000 | 0 | 4500 |

and if the table is mess please look at the image below:



The question is, how [saldo] field can calculate it automatically when i change value each row of the [IN] or [OUT] field?

Please help me
Dec 31 '16 #1

✓ answered by jforbes

There's not a reliable way in Access to have this done automatically. Typically, this isn't something that is done in a Database. Usually calculated fields are calculated when they are needed instead of calculating them and storing them in a table. But there are exceptions and times when storing a calculated field in the table is preferred. In those cases, with a running total, the best solution I've seen is one that performs an update on either the entire table or on an entire group of Records.

Here is an example function to update the entire table:
Expand|Select|Wrap|Line Numbers
  1. Public Function updateTotals() As Boolean
  2.     On Error GoTo ErrorOut
  3.  
  4.     Dim oRst As DAO.Recordset
  5.     Dim sSQL As String
  6.  
  7.     Dim dTotal As Double
  8.  
  9.     dTotal = 0
  10.     sSQL = ""
  11.     sSQL = sSQL & "SELECT * FROM TempTotals ORDER BY ID"
  12.     Set oRst = CurrentDb.OpenRecordset(sSQL)
  13.     While Not oRst.EOF
  14.         dTotal = (dTotal + Nz(oRst!in, 0)) - Nz(oRst!out, 0)
  15.         oRst.Edit
  16.             oRst!Saldo= dTotal
  17.         oRst.update
  18.         oRst.MoveNext
  19.     Wend
  20.  
  21.     updateTotals = True
  22.  
  23. ExitOut:
  24.    Exit Function
  25.  
  26. ErrorOut:
  27.     MsgBox Err.Description
  28.     Resume ExitOut
  29. End Function
This function will run slower and slower over time as records are added.

Once you have a function like this, you will need to find the best way and time to run it. Either after a record is updated or right before you show the totals. Sometimes people have something like this run at night when it's gonna take a long time.



The other way to perform the Calculation on demand is to create a Query that Sums [In] and subtracts the sum of [Out]:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.   TempTotals.ID
  3. , (SELECT Sum(TT.[in])-Sum(TT.[out]) FROM tempTotals AS TT WHERE TT.[ID]<= TempTotals.ID) AS CalculatedSaldo
  4. FROM TempTotals
This will run a little slower over time, but unless you run it wide open, you would only be calculating a subset of the records, meaning it's likely to be quick enough to be usable.

Share this Question
Share on Google+
1 Reply


jforbes
Expert 100+
P: 1,107
There's not a reliable way in Access to have this done automatically. Typically, this isn't something that is done in a Database. Usually calculated fields are calculated when they are needed instead of calculating them and storing them in a table. But there are exceptions and times when storing a calculated field in the table is preferred. In those cases, with a running total, the best solution I've seen is one that performs an update on either the entire table or on an entire group of Records.

Here is an example function to update the entire table:
Expand|Select|Wrap|Line Numbers
  1. Public Function updateTotals() As Boolean
  2.     On Error GoTo ErrorOut
  3.  
  4.     Dim oRst As DAO.Recordset
  5.     Dim sSQL As String
  6.  
  7.     Dim dTotal As Double
  8.  
  9.     dTotal = 0
  10.     sSQL = ""
  11.     sSQL = sSQL & "SELECT * FROM TempTotals ORDER BY ID"
  12.     Set oRst = CurrentDb.OpenRecordset(sSQL)
  13.     While Not oRst.EOF
  14.         dTotal = (dTotal + Nz(oRst!in, 0)) - Nz(oRst!out, 0)
  15.         oRst.Edit
  16.             oRst!Saldo= dTotal
  17.         oRst.update
  18.         oRst.MoveNext
  19.     Wend
  20.  
  21.     updateTotals = True
  22.  
  23. ExitOut:
  24.    Exit Function
  25.  
  26. ErrorOut:
  27.     MsgBox Err.Description
  28.     Resume ExitOut
  29. End Function
This function will run slower and slower over time as records are added.

Once you have a function like this, you will need to find the best way and time to run it. Either after a record is updated or right before you show the totals. Sometimes people have something like this run at night when it's gonna take a long time.



The other way to perform the Calculation on demand is to create a Query that Sums [In] and subtracts the sum of [Out]:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.   TempTotals.ID
  3. , (SELECT Sum(TT.[in])-Sum(TT.[out]) FROM tempTotals AS TT WHERE TT.[ID]<= TempTotals.ID) AS CalculatedSaldo
  4. FROM TempTotals
This will run a little slower over time, but unless you run it wide open, you would only be calculating a subset of the records, meaning it's likely to be quick enough to be usable.
Jan 3 '17 #2

Post your reply

Sign in to post your reply or Sign up for a free account.