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.

 

[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.

 

[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