Creating Loan Payment database

July 2, 200322 yr

I have a relational db with a couple of db's USER and LOAN. The LOAN db holds term, and monthly payment amounts.

What I want to do is create a third db called LOANPAYMENTS that will hold individual "invoice" records for each monthly payment for each user, so that I can forecast collections by month. I could do this manually of course, but I'm sure this is not a new issue. :-)

Ideally, once I enter a new record in LOANS, I could write a script that creates X number of records in PAYMENTS, where X equals "term" in LOANS. Loans db also has month of first payment, so script would then also use that value as due date for first payment record, then add a calendar month for each subsequent.

Are there templates for this somewhere? Thanks for any help.

July 2, 200322 yr

Try: www.fmfiles.com/enduserbus4.html

The script you described wouldn't be hard to construct. You'd want a global number field in the payments file to use as a "counter." Set the counter to the number of payments, set the start date, then loop, adding one to the month and subtracting one from the counter until it's zero.

July 7, 200322 yr

Author

Thanks. What if the term isn't global, i.e. each loan has a different number of months to pay?

July 7, 200322 yr

The global is just to use as a temporary counter in the script that generates records in the payments file. You'd still keep the "number of months" as a standard number field in each loan record.

July 13, 200322 yr

Fitch

I love your graphic.

John

July 18, 200322 yr

Author

Thanks a lot, Tom!

I've actually got the whole thing almost working based on your suggestions. My one problem is an issue with calculating the new due date. Result should be in date format and I have the calculation set as this:

GlobalPaymentMonth & "/" & DueDateDay & "/" & DueDateYear

However, it's just coming out plain wrong. The DuDateDay is a different number and the duedateyear comes out wrong also. Am I doing something wrong in trying to create the new date through a calculation forumla like the above? I assumed I could just create number/number/number and set result as date format.