Balance Field in MS Access

[raketekc]

hi Guys,

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?

Please Help!!!!

[RobDog888]

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.

[raketekc]

Hi, thanks for response

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.

please help am going crazy with this!!

[Hack]

How do you calculate the bottom balance?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.

[leinad31]

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.

[leinad31]

And using credit and debit instead of one field makes it easier to check for erroneous entries... ask any accountant.

[raketekc]

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.

[leinad31]

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.