Calculate on the fly vs update query

[Janna]

I’m wrestling with designing an Access database to track investments over a
period of time and would be grateful for any advice. This db is to replace an
independent program that the software maintenance fee has just gotten too
expensive to continue. I apologize for the epic, but here goes:

Simplified flow is as follows:
An investment is purchased and the following data is recorded into
appropriate fields:

Purchase price
Purchase date
Maturity date
Financial institution
Security Type
Unique Security ID
Interest rate
Days used to calculate interest
How interest is paid (quarterly, semi-annual, monthly, etc)

With the old program, throughout the year, as interest was paid and perhaps
added back to the principal, or the investment matured, the user would bring
up the investment record in the program and have fields for

Date of change
Interest Received
Principal Change
Res Amount
Remark field.

Any interest received would be recorded in the Interest Received field.

If the Interest Received was also reinvested into the original security, the
amount was entered into the Principal Change field which would increase the
total current value of the investment (purchase amount + Principal Change).

If the security matured, a negative amount, equal to the total current value
of the investment was entered into the Res Amount field so the value of the
investment became 0 (purchase amount + Principal Change + Res Amount)

To track the investments, I thought I should have two main tables, one for
the initial investment data and then another to record daily/weekly/monthly
transactions (one-to-many relationship tied together with an investment id
field)

The problem I’m running into is that with the old program, the user could
run a transaction history report for a selected date range, that would list
each change for the selected time period by individual security with an
“Original Amount†(amount of the investment before the last change was made),
the change amount, and then the new amount, after the change occurred. When
I first started the project I thought it would be best to just calculate the
necessary report values on the fly, but how can I accomplish that if my
“original amount†is actually a total of the purchase price plus all the
additions of Interest that have occurred up until the reporting period.

Simplified example, the user runs a transaction history report for the month
of September. On September 2, 2006, Interest of $25 was paid toward an
investment that was originally purchased for $1000 on 1/2/06. In every month
since purchase, (except January) $25 interest has been paid and added back to
the original investment. The transaction report ran for September would show:
Date of Change Original Amount Principal Change Balance
9/2/06 $1175 $25 $1200

In October when the transaction history report is run, $1200 would become
the "original amount".

How do I develop a query or calculated control in a report to accomplish
this? Or, should I not try to be calculating everything on the fly? Should
there be an update query run after interest is added back into a separate
field called original amount? I just am not sure on the best way to approach
this… Any suggestions appreciated.

 

[Damian S]

Hi Janna,

If you are storing a field called OriginalInvestment or whatever, why are
you updating that field? You then lose track of what the Original Investment
amount actually was!!!!

Generally speaking, you should calculate stuff on the fly where possible...
You generally don't store something that you can calculate, unless the
database is so big and the calculation so complex that it's more efficient to
store it somewhere.

How about having an OriginalInvestment field that never changes, and a
CurrentInvestment field that holds the current value of the investment. That
way, you can tell at a glance what the OriginalInvestment was, and the
current value, and you can use the OriginalInvestment PLUS the Interest etc
to calculate the value at a point in time.

Hope this helps.

Damian.

 

[Janna]

Hi Janna,

If you are storing a field called OriginalInvestment or whatever, why are
you updating that field? You then lose track of what the Original Investment
amount actually was!!!!

Generally speaking, you should calculate stuff on the fly where possible...
You generally don't store something that you can calculate, unless the
database is so big and the calculation so complex that it's more efficient to
store it somewhere.

How about having an OriginalInvestment field that never changes, and a
CurrentInvestment field that holds the current value of the investment. That
way, you can tell at a glance what the OriginalInvestment was, and the
current value, and you can use the OriginalInvestment PLUS the Interest etc
to calculate the value at a point in time.

Hope this helps.

Damian.

 

[Janna]

Thanks. Makes sense. Have you ever done this and can you suggest code
and/or process to do update to the CurrentInvestment field? Could it just be
done with code as an "afterupdate" event? Thanks again.