am using ms access 2003 for my vb6 appliction that has a table for a bank account where all the deposits to the account are entered. Now i want to derive a balance after each entry. i have done it in vb but the problem is that once you make a mistake on one entry, the final balance will be wrong.
it there a formula in vb or ms accesss that can derive the balance as entries are made and still be able to update the balance once an entry is edited?
How would you know which entry to make the adjustments on? You can not adjust the balance if it is the sum of a field in your table. You need to identify the offending entry and correct that one so when its added up agian its correct.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
am not quite sure i understand you, My code goes a little something like this:
open database and get last balance
add depopsit amount to balance
addnew to table with new balance
now if i have made 10 entries then i want to edit entry nummber 1, when i edit, the last balance at the bottom will remain the same, hence my question of whether there is a way that the table would adjust the balance field after makeing a change.
it there a formula in vb or ms accesss that can derive the balance as entries are made and still be able to update the balance once an entry is edited?
There is no built in formula, but calculating, it seems, would be as simple as:
OldBalance + NewBalance = TotalBalance
If you edit NewBalance, and re-run the forumla, a new total will be created.
If you had designed the database with two fields: credit and debit, then you would have been able to create an additional field that is a subquery for the running balance.
The subquery would be something like...
select table.*, (Select (Sum(debit) - sum(credit)) FROM table_alias where table_alias.datetime <= table.datetime and table_alias.id <= table.id) as runbalance
from table
You have to have a means of identifying which records come before the current record... I used date and time combioned with autoinc id (on second thought you can use just that (without the date) instead). table_alias is the same table but contains records only up to the current record in table.
Last edited by leinad31; Oct 4th, 2005 at 02:50 AM.
Thanks guys for your response but i can't seem to get it. My tabel is like this
ID DETAIL DEBIT BALANCE
B/forward $300
1 JOHN $100 $200
2 PETER $50 $150
3 ANDREW $50 $100
Now if i make a change to entry number 2 (PETER), say i put $100 , then my closing balance remains $100 instead of $0. Now i want when i change entry number 2(PETER) the balance field to automatically adjust itself.
I hope this illustration will help you to see my situation. If there is a way around this please help me get round it.
Last edited by raketekc; Oct 12th, 2005 at 10:36 AM.
No your closing balance would be 50 not zero cause you increased peter's debit by 50 not 100... but any way. for it to update automatically, you won't store the balance in the table with the debits/credit. You create a stored query like in the attached db.
In the sample stoerd query [Runnign_Balance], [ID] was the field used to determine the ordinal value of each entry. By ordinal, we describe its position/place in a sorted list so we know which comes first when calculating the running balance. The limitation with using an autonumber for the ordinal value is we can't "insert" items in the sort list, as in the case of an entry being posted late due to negligence..it will have to be inserted in the middle and not appended... but due to the nature of autonumbers it wil be appended by the sample stored query [Running_Balance]
But the problem with using date as the ordinal value criteria is we have to ensure it is unique... up to the milliseconds if possible... I'll let you figure that one out youself.
Reply With Quote
Originally Posted by raketekc
