Running Balance

[AG]

Using Access 2003.
Two tables.
tblBank - BankID (autonumber PK), PersonId (Long integer), MonthDate
(DateTime), DepositAmt (currency)
MonthDate is always first of month. Unique index PersonID, MonthDate.

tblExpenses - ExpID (autonumber PK), BankID (Long Integer), ExpAmt
(Currency).

One to many relationship tblBank.BankID on tblExpenses.BankID

Each month a DepositAmt is entered.
If the (prior balance + DepositAmt) >= Sum(ExpAmt for that month), then
new balance = (prior balance + DepositAmt) - Sum(ExpAmt for that month)
otherwise, the new balance is zero.
The new balance becomes the prior balance for the following month.
Any remaining ExpAmt is NOT carried over to the following month.

I need to be able to get the current balance and the total ExpAmt applied
for any month.

I can only think of two ways to do this.
Add another field to tblBank to store the prior balance (recalculating when
data changes)
or
copy all the necessary data into a temp table and walk through all the
records, recalculating every time I need to get the balance.

Both methods have their drawbacks with data integrity and performance.

Can anyone else offer another suggestion?

Thanks in advance for any help.

 

[Roger Carlson]

I'm not certain this will help, but on my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"RunningSumInQuery.mdb" which shows how to create a running sum in a query.
Perhaps this will give you some ideas.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Charles Wang[MSFT]]

Hi AG,
To let me better understand your issue, I would like to know:
1. How do you know the balance of your first month?
2. What is the relationship between MonthDate and BankID?
3. What is the relationship between MonthDate and DepositAmt?

The 2nd and 3rd questions are helpful for letting me understand the
comparision between (prior balance + DepositAmt) and Sum(ExpAmt for that
month).

I think that there is a recursion here. However Access SQL does not provide
good support for handling recursion. If the balance of the first month can
be known, it is not difficult to know the balance of the 2nd, 3rd,... and
12th month. After that, you can perform a UNION to combine those results
into one list.

If you have any other questions or concerns, please feel free to let me
know. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[AG]

Thanks Roger, I will investigate that.

 

[AG]

Thanks for the reply Charles.

For the first month, the prior balance would be zero. So the available funds
would be 0 + DepositAmt.
Therefore, the balance for the first month would be IIF(Sum(ExpAmt)<=(0 +
DepositAmt),((0 + DepositAmt) - Sum(ExpAmt)),0). That balance would become
the 'PriorBalance' for the following month.

BankId is the PK for tblBank.
There can only be one record for each combination of PersonID and MonthDate.
Since MonthDate will always be the first of the month, there will only be
one record in tblBank for each PersonID for each month.
Therefore BankId provides a single field identifier for each combination of
PersonID/MonthDate.
DepositAmt is the amount added to the 'Bank' each month.

Here is the actual application. It is a method of reimbursing salespeople
for their expenses.
A new salesperson joins a company in Jan, and a 'Bank' is started with an
initial 'Deposit'.
Since they are new, there is no 'Prior Balance', so the 'Available Funds' is
equal to the 'Deposit'.
The salesperson has several expenses for Jan.
If the expenses total less than or equal to the 'Available Funds', they are
subtracted from the 'Available Funds' and the result is the 'New Balance'
for Jan.
If the expenses are greater than the 'Available Funds', then the 'New
Balance' for Jan is zero.

Whatever the 'New Balance' for Jan is, it becomes the 'Prior Balance' for
Feb.
In Feb, the 'Available Funds' are equal to the 'Prior Balance' plus the new
'Deposit' for Feb.

So, a salesperson can be reimbursed for their expenses for each month, up to
the amount of their 'Available Funds' for that month, but they can not carry
over expenses to another month.

 

[Charles Wang[MSFT]]

Hi,
Thanks for your response.

So I understand that PersonID and MonthDate are one-to-many relationship in
tblBank. For this complex requirement, it is hard to write a simple SQL
statement to work around your issue. I think that your first method is a
good way, actually a common practice in normal database design, to resolve
this issue:
"Add another field to tblBank to store the prior balance (recalculating
when data changes)".

Could you please let me know what your concerns are regarding this method?

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[AG]

Charles,

I guess I will need to add the new field. I was just wondering if there was
another method, that I had not thought of.
My only concern with it is that I thought 'good design' avoided storing
'calculated' values.

 

[Charles Wang[MSFT]]

Hi,
Thanks for your response.

Generally we are guided to avoid storing calculated values in designing a
database; however it is not always true. Sometimes for performance
optimization or resolving some special needs, we need to break the rule.
This is same as that we may not always abide by database normalization when
designing a database. However this does not mean that your database design
is not good.

Please feel free to let me know if you have any other questions or
concerns. Have a good day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[AG]

Thank you.