Question Create multiple Records from 1 Record

[GohDiamond]

Hello,

I have to produce a loan fee schedule with declining fee calculations applied every 12 months. I can do all the calculation formulas and come up with a single result for each 12 months.

For example in the first 12 months the fee is 600/month
For the next 12 months the fee is 550/month
and so on for a total of X months (Total number of months varies)
Some Loans are 360 months (30 years)

What I have is:
LoanID FeeCalc1 feeCalc2
Loan1 600 550

What I need is:
Loan1 Month1 FeeCalc1
Loan1 Month2 FeeCalc1
Loan1 Month3 FeeCalc1
. . .

Loan1 Month13 FeeCalc2
Loan1 Month14 FeeCalc2
Loan1 Month15 FEeCalc2
. . .

There over 41k loans to be processed this way resulting in about
1.6 million records

How would I create this output table from the calculation either in vba or Sql.

I'm kinda lost so any help would be appreciated.

BTW I have about a week to resolve this.

Thanks for any help in advance
CHEERS!
Goh

 

[Privateer]

Well, one problem is your fee table is based on a year, and the loan table is based on a month. And you have no dates involved, which may be OK because loans start in any month, so month 1 doesn't mean January.
One suggestion would be to change your fee table and include a month number. Unfortunately, each loan could be 360 rows long, (more or less) which would result in a long table, not necessarily a big one. I suggest this because I don't know how many fields a table can have, so going verticle is a better option.

To create the new fee table, you can use an append query. I start with a make table query and put all the FeeCalc1 values in, with a month value = 1. Then use the append query to put the values of FeeCalc2 under FeeCalc1, etc. Just remember to change the month. When you put FeeCalc2 in, the month value is two for all the records. The month for FeeCalc3 is three for all records, etc. So the new fee table looks like this:

LoanID Month Fee
1 1 600
1 2 600 ...
1 12 600
1 13 550
1 14 550 ...
1 24 550
1 25 500

Then in a query you draw a line from tblLoans.LoanID to tblFees.LoanID and another line from tblLoans.Month to tblFees.Month. This will match everything up in a query.

The coding answer involves "for loops" and other code to put this all together. You would need the start date and duration and do a "for loop" from 1 to 360 append this info into a table with this loanID, the counter number (1-360) as the month and the appropriate fee. That answer is beyond this forum. Hopr this helps
Privateer