1

I have a table loaded in Access (although the original file is a txt.file) with some loan characteristics and would like to create another table containing the cash-flows of these loans (“how much money are received every X month). Here is a sample of my original table:

ID ---- LoanNbr ---- EntryDate ------ ClosingDate ----- Amount ---- Repayment ---- Frequency.

1 ----- 101 ---------- 1-1-2010 -------- 31-12-2012 ------ 1000E ------ 100E ------------ 1Month

2 ----- 102 ---------- 15-6-2012 ------- 31-12-2014 ------ 5000E ------ 250E ----------- 3Month.

....

And here is a sample of the output for loan 1:

ID ----- LoanNbr ----- EntryDate ----- Period --------- BeginLimite ----- Repayment ----- EndLimite.

1 ------ 101 ----------- 1-1-2010 ------- 01-2010 ------ 1000 ------------- -100 --------------- 900

1 ------ 101 ----------- 1-1-2010 ------- 02-2010 -------- 900 ------------- -100 --------------- 800

1 ------ 101 ----------- 1-1-2010 ------- 03-2010 -------- 800 ------------- -100 --------------- 700

...

The calculations in themselves are not heavy at all, I just haven’t figured out how to perform them using a DBMS. I am also opened to other suggestions concerning software/language that could be used with this set up.

Thanks in advance! Jack

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Batmax
  • 253
  • 8
  • 17

1 Answers1

0

There're a few major ways of doing that:

  1. You can create a query a field of which will be calculated. https://support.office.com/en-gb/article/Add-a-calculated-field-to-a-table-14a60733-2580-48c2-b402-6de54fafbde3
  2. You can extend the data by adding and extra column, create a VB script, which will run against the table data and insert the calculated value in a field. Set value for all rows in a datatable without for loop I would go with a simples approach which is the 1st way of doing it.

But there're advantages and disadvantages to both.

Community
  • 1
  • 1
amdmax
  • 771
  • 3
  • 14