Compare two records

[Guest]

I have a database that tracks payments on loans going back over 10 years.
One table has loan information...another has payment information, by date.

A report exists that essentially starts with the beginning balance of the
loan...then lists each payment and creates a running total of the balance.

I want to add a component to this running total that calculates interest
due, by month, in the report.

To do this, I need to compare the date in each payment record to the date in
the record just previous.

What I am having trouble with is trying to figure out how to create a
'stored' value so that the report can compare the current date to the date on
the line above....or retrieve a value from the previous record...

The issue is that there are over thousands of payments in the db for many
loans. Often, months will go by with no activity so I need to compare the
balance at the last payment to the current payment and apply
interest....based on the number of days that were between the two records.

example:
Date Pmt Prev Date % Int
11/1/05 -100 9/15/05 .05 7.50
12/1/05 -100 11/1/05 .05 5.00


If you have any ideas...I would appreciate it.

 

[Allen Browne]

Kevin, I've done one of these, and it ended up being considerably more
complex than you describe here.

For example, the bank can change the interest rate on any date. That means
the interest payable for the period between 2 payments, is not necessarily
at ONE interest rate. Then there are penalty rates (where a payment is
late), along with the issues relating to when a payment is "late" (e.g.
where a monthly-payment loan was taken out on 31st of month, or where the
month end falls on a weekend/public holiday), and some banks even tier the
penalty rates based on amount and/or longevity of lateness. Then there are
pre-payments for amounts due on future payment dates, as distinct from
additional payments (where the future payments are still due on their
expected dates.) Then there is the question of whether interest is charged
on the daily balance, or some other basis (such as the minimum/maximum
monthly balance.)

The issues are more complex than can be calculated on the fly in a report.
You will need to create a temp table to hold the balances and interest
charges, and then base the report on this temp table.

 

[Guest]

Allen - all of your comments are valid...but this is very straightforward and
has nothing to do with financial institutions.

The root of my question is this...how can I store a value during a report
'loop' so that I can reference it the next time around...temporarily store a
value for a calcualation the next time around...then overwrite that value
with the current value.

Seems like it should be possible...I might just be making this harder than
it is.

Thanks for your advice.

 

[Allen Browne]

You can create variables (in the General Declarations section of the
report's module), assign a value in the Print event of the section (so it
remembers the previous value), and then read it an use it in the Format
event of that section.

That works if your report is only 1 page long. If you try to do that over
multiple pages, you run into a problem if the user only prints some of the
pages of the report, because the events may not fire for all the intervening
pages, and so your amounts are not correct.

A more reliable approach would be to build a query that supplies the values
from the previous row as well as the current row. You can use a subquery to
get a single value from the previous row (such as the date). That can give
you a "multi-level group by error" when you try to create a report on that
query if you then try to perform any aggregation on that field. If that
happens you could use one of the domain aggregate functions such as DMax(),
but that will perform slowly.

For an explanation on how to create such a query, see:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504/en-us

Once you get the query working, you will presumably use the Running Sum
property of (hidden?) text boxes on your report to aggregate the payment
amounts and interest charges, and so arrive at a balance.