recurring interest

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hello all -
I am in the midst of finishing up a customer database. I was now asked if I
could automate a process where the Access database will assess automatically,
on the 30th day from the loan date, a finance charge and then assess a
finance charge every 15 days thereafter until the balance on the loan is
zero. Anyone have a suggestion on how I would do that? Again, they want
this automated without user intervention. Any help is greatly appreciated.
Lisa C.
 
"Without User Intervention" is not that big a hurdle. There are different
ways to do that, depending on your needs.
One would be to kick off the process when the database opens, but that would
slow down and frustrate your users, and it would be difficult to determine
whether it had already be run for a specific day. The same would be true for
doing it when the database is closed.
Another way would be to create a stand alone mdb that has only the objects
in it necessary to execute the interest application. Use the Windows task
scheduler to open the mdb, and either an Autoexec macro or a specified
startup form that would run the calculations.

Other issues to consider.
If you use the autoscheduler, you will need a way to let somebody know the
process was successful or there were errors.

Assuming a 5 day work week, There will be 2 days each week when the process
is not run. For example, tomorrow is 11/11/2006. If there are loans where
that need to be calculated where the calculation date is either 10/11/2006 or
10/26/2006, they will be missed.

The actual doing of it is not that difficult but before you begin, I suggest
you get rules defined on how you will handle week ends and how you will
execute the process. It would be much safer and eaiser if somebody just
clicked a button on a form in your application.
 
Yes, Klatuu, that would be easier but they are against anyone being
responsible enough to remember to click a button, ridiculous I know. If I
convinced them to click a button, how would you write the procedure/queries,
using loanbegindate, loanamount, loaninterest?
 
I don't know your data structure enough to actually write any code for you,
but here are some pointers. One thing I don't know is whether you know when
the last interest calculation was done. You did say 30 days from origination
date then every 15 days after that. The 30 days from origination is simple.

LoanDate = DateAdd("d", -30, Date())

The every 15 days needs more clarification.
 
ok, here's a scenario. Loan origination date is 10/1/06, first interest
would be 10/31/06. Next interest on this would be 11/14/06, then 11/28/06,
then 11/12/06 etc., until the balance of the loan is paid off and there is
zero due.
 
That part I understand. The question is, how do you know when the interested
was last calculated and applied?
Note, you originally said 15 days, but the dates you show here are 14 day
intervals.
 
My bad, yes, 15 days.
I was thinking of doing a query, group by, putting 'last' on the date field,
criteria for the interest id and pull any loan with a balance due. Although
it may take two or three to get there, I'm sure I can do it.
My real fear is the automation, I wish Access had an internal scheduler. My
only option is Windows Scheduler as far as you know?
 
It is possbible to put the scheduling within your application; however, that
means that someone will have to run the application every day. This, in
reality, is the similar to someone having to click a button every day.

To do this, you would have to put the calculation routine in your startup
routines. But, you would also need to check to see if the calculation has
already run for the current date. The disadvantage of this approach is that
it will slow down getting into the application, particularly for the first
person to open the databse in the morning. Users will complain about this.

Now, I still need an answer to my question to be able to help:

Do you have any way to know when the last time interest was applied to a
specific loan?
 
Back
Top