How to Update fields based on values of other fields

[dbain30]

I'm attempting to create a report that will provide our customers with
an account history and accrual information. So far I've created a
report that list all of the transactions and has a running balance
however I'm having trouble in calculating the accrual.

Each transaction is posted with an Effective From date, the issue is
that in order to calculate interest you must be able to calculate the #
of days in that period. The only way to do that is to look at the date
of the next transaction and calculate the difference between the two.
Since these are two records on the same table, and will change each
time we run the report, I have yet to figure out how to accomplish this
task.

A second issue is that most of the transactions are principal, interest
and fee payments. For these items I would need the interest rate from
the previous transaction to flow down unless the transaction is a rate
change, then it should remain as is and then follow the same logic for
remaining transactions.

Any help that you can offer would be greatly appreciated!

 

[Allen Browne]

You can get the value from the next record like this:
http://support.microsoft.com/kb/210504/en-us

But as you state, the issue is more complex than that, as the interest rate
can change an any time as well. It gets more complex again if you have
tiered interest rates (e.g. penalty rates if the debt it beyond the
limit/overdraft), the possibility of changes to these limits at any time,
rates based on min/max monthly balances rather than daily balances, and so
on. Last time I calculated one of these, there were so many variables that
could change at any time that the only way forward was to use a temporary
table to calculate the interest for each day. After optimizing the code, it
came down to about 2 seconds to calculate an entire account for a period of
years, and you can then group/sum these values into the report.

 

[dbain30]

Thanks! I'll try that as soon as I get into the office tomorrow!

You are correct, the situation is more complex with tiered rates
however in our accounting system each rate is assigned a specific
charge code. For those items I'll just have to sort by Charge Code and
then list the net difference.

Thanks again!

 

[dbain30]

Allen,

Again, thanks for the help!

I was able to use the DLookUp formula and I'm very close to making this
work.

The area that it did work is that it updated a "thru date" for each
transaction so that I can calculate the # of days, for that it worked
perfectly. The issue that I'm still having is that for the interest
rate I included an IF statement so that if there's a transaction to
change the rate that it accepts that and would carry it forward.

Here's the formula:
Rates: IIf([Rate] Is Not Null,[Rate],DLookUp("[Rate]","History
Transaction Table Numbered","[Number]=" & [Number]-1))

The first record contains the rate information and this is working
perfectly for the 2nd record however the remainder of the records are
still blank. I thought that this would cascade the rate information
down from one record to the next, assuming that the rate is null. Do
you have any suggestions that would continue this process for each
record?

Again, any help that you can offer is greatly appreciated!

 

[Allen Browne]

The DLookup() for Rates would be okay if you need exactly the previous
number, but I am not sure I would built the history table that way.

Unless you are doing individual rates that vary from person to person, I
would anticipate a Rates table that has fields such as:
RateID primary key
AccountType kind of account this entry applies to
FromDate first date this rate becomes effective
Rate percentage to charge for this account type from this
date.

You would then retrieve the rate applicable to an account type on any
particular date the way that Tom Ellision explains in this article:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html
It's a little involved, but Tom explains how to get something that is
normalized, efficient to execute, and easy to maintain.

 

[dbain30]

Thanks! I tried that today and it worked great!

I'm still just having one issue. I'm using the following line in my
query to create a table that includes the "to" date based on the
effective date of the next transaction:
Effective To: DLookUp("[Effective Date]","History Rate Table","[Rate
Number]=" & [Rate Number]+1)

It is working, I'm receiving the correct data in the field however it's
coming over as text, therefore when I'm comparing the two dates to
determine the number of days, I'm getting an error message. Is there
any way to resolve this? Based on a suggestion from a friend at work I
tried to both set the property as "Short Date" in the query and even
tried using the following code to no avail.

Effective To: Format(DLookUp("[Effective Date]","History Rate
Table","[Rate Number]=" & [Rate Number]+1),"mmddyyyy")

Thanks again for all of your help! I've spent a few days on this and
so far 90% of the progress is the result of the two responses that you
provided!

 

[Allen Browne]

Assuming that the target field is a date/time field, we need to convince JET
to understand the query field as date/time also. CVDate() should do it:

Effective To: CVDate(DLookUp("[Effective Date]","History Rate Table","[Rate
Number]=" & [Rate Number]+1))

More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

 

[dbain30]

Allen, I just wanted to say Thanks!!! I spent a lot of time putting
this together and I can definitely say that your posts drastically
reduced that effort and at the same time taught me a lot about how to
build applications within Access. I've put together 15-20 databases at
work and with a few things that I've learned, I'm now debating on
whether I want to go through the effort of upgrading some of those in
the next few weeks.

Thanks again! Have a great holiday weekend!

 

[Allen Browne]

Comments appreciated.

Have fun if you decide to go with the updates.

There is certainly a heap to learn with Access, since it gives you so many
possibilites. And just when you think you have it down, they release a beta
for a new version that has *lots* of new functionality:
http://www.microsoft.com/office/preview/