Running Balance Help wanted

[bobdydd]

Hi Guys Can anyone help

I am trying to find a way to implement a running balance that will
re-calculate if the date order is changed, or if an earlier record is
changed like you might want to do during a bank statement reconcile.

I am using the following fields

Date|Payment|Deposit|Running_Balance (tblTransactions)

While the records remain in their original order, the running balance
makes sense, but if I move a record to an earlier date, or alter an
earlier record then the running balance no longer makes sense.

I am only experimenting at the moment for a project that starts in
July, so I would appreciate a prod in the right direction.

Regards Bob Dydd

[Douglas J. Steele]

You shouldn't be storing the balance. Instead, you should be calculating it
in your query.

How you do that, though, can be slow.

One way is to use DSum("[Deposit]", "MyTable", "[Date] <=" & Format([Date],
"\#mm\/dd\/yyyy\#")

BTW, you should rename your field from Date: that's a reserved word, and you
can run into lots of problems using reserved words.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"bobdydd" <re**************@yahoo.co.uk> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Hi Guys Can anyone help

I am trying to find a way to implement a running balance that will
re-calculate if the date order is changed, or if an earlier record is
changed like you might want to do during a bank statement reconcile.

I am using the following fields

Date|Payment|Deposit|Running_Balance (tblTransactions)

While the records remain in their original order, the running balance
makes sense, but if I move a record to an earlier date, or alter an
earlier record then the running balance no longer makes sense.

I am only experimenting at the moment for a project that starts in
July, so I would appreciate a prod in the right direction.

Regards Bob Dydd

[bobdydd]

Thanks for the prompt reply Doug

I took on board what you said about naming fields "Date" and made an
alteration in my experimental mdb so that the fields now are just

TransactionID | TransactionDate | Amount | and they are in
(tblTransaction)

I have made a series of expressions:

1. The Microsoft's Basic DSUM in the help system
DSum(expr, domain[, criteria])help systems basic DSUM

which when I use my fields becomes
Balance: DSum("Amount","tblTransaction","TransactionDate")

This just shows a total balance of ALL numbers

2. An alteration to this using an alias based on the TransactionID as
an extra field in the query:
Balance: (DSum("Amount","tblTransaction","[TransactionID]<=" &
[TransactionAlias] & ""))

This worked quite well and when I altered or added a figure in the
amount field the running balance correctly recalculated (after
refreshing)

However the fly in the ointment is that if you alter the date of the
latest entries as you would if you were reconciling a bank account (eg:
One check cashed earlier than another) the balance that was showing
when the entry was originally made is the one that remains with that
entry in it's NEW position.

This means that the query does not show the current balance at the
LATEST Date.

Regards Bob Dydd

[Douglas J Steele]

I believe I suggested basing DSum on the date, not the Id.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"bobdydd" <re**************@yahoo.co.uk> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

Thanks for the prompt reply Doug

I took on board what you said about naming fields "Date" and made an
alteration in my experimental mdb so that the fields now are just

TransactionID | TransactionDate | Amount | and they are in
(tblTransaction)

I have made a series of expressions:

1. The Microsoft's Basic DSUM in the help system
DSum(expr, domain[, criteria])help systems basic DSUM

which when I use my fields becomes
Balance: DSum("Amount","tblTransaction","TransactionDate")

This just shows a total balance of ALL numbers

2. An alteration to this using an alias based on the TransactionID as
an extra field in the query:
Balance: (DSum("Amount","tblTransaction","[TransactionID]<=" &
[TransactionAlias] & ""))

This worked quite well and when I altered or added a figure in the
amount field the running balance correctly recalculated (after
refreshing)

However the fly in the ointment is that if you alter the date of the
latest entries as you would if you were reconciling a bank account (eg:
One check cashed earlier than another) the balance that was showing
when the entry was originally made is the one that remains with that
entry in it's NEW position.

This means that the query does not show the current balance at the
LATEST Date.

Regards Bob Dydd

[bobdydd]

I have looked at 2 of the Access based Accounting Systems

1. "Yes I Can" from Database creations
2. "Image Acoounting" from Comtech

Both of these systems are designed by men of huge genius and their
answer to the problem of an updateable running balance is NOT TO HAVE
ONE.

A DSUM calculation appears at the bottom of the form which does the
job, but it looks very unfamiliar to a NON ACCESS person who is used to
seeing his UPDATEABLE running balance by the side of each transaction,
as it Qucken, Microsoft Money, Spreadsheet etc.

Anyway, it has got me stumped, but it was a good mental exercise.

Regards Bob

[stevemanser]

My solution is to have a "Recalc" button that when pressed goes through each record in the recordset of the form, and adds or subtracts values to the current "running_total" field.

Because your form is going to be different to mine, take this code and customise it to your needs. First, the piece of code below allows you to go through the form and affect each record:

With Me.Recordset
If .RecordCount > 0 Then
strBookMark = .Bookmark
.MoveFirst
Do Until .EOF
.edit 'this line is only needed for DAO, delete it if you're using ADO

[*** YOUR MANIPULATION CODE HERE ***]

.Update
.MoveNext
Loop
.Bookmark = strBookMark
End If
End With



For example, my code actually looks like this:

Private Sub Recalc_Click()
x = DLookup("id", "list_accounts", "account_name='Bank'")
z = DLookup("id", "list_accounts", "account_name='Purchases'")
a = DLookup("id", "list_accounts", "account_name='Expenses'")
With Me.Recordset
If .RecordCount > 0 Then
strBookMark = .Bookmark
.MoveFirst
y = Me.trans_amount
Me.running_total.Value = y
.MoveNext
Do Until .EOF
.edit 'this line is only needed for DAO, delete it if you're using ADO
If Me.debit_account = x Then
' if money going into bank account
Me.running_total.Value = y + Me.trans_amount
ElseIf Me.credit_account = x Then
'if money going out of bank account
Me.running_total.Value = y - Me.trans_amount
ElseIf Me.debit_account = z Then
'if money going out on purchases
Me.running_total.Value = y - Me.trans_amount
ElseIf Me.debit_account = a Then
'if money going out on expenses
Me.running_total.Value = y - Me.trans_amount
Else
Me.running_total.Value = y
End If
y = Me.running_total.Value
.Update
.MoveNext
Loop
.Bookmark = strBookMark
End If
End With
End Sub

Steve