value copied

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

Guest

How do I transfer the result of my query in field Principal1 to continue the
calculation from the Principal + Interest of the previous month and having it
compounded from a month to the second month?


SELECT DISTINCT Right([DB_FROM],7) AS DB_YEARMONTH,
[Forms]![Interest_Christine]![Principal] AS Principal1,
Sum((([db_prime]/100)/365*[No_of_Days]*[Principal1])) AS Principal_Int
FROM [INTEREST TEMP]
GROUP BY Right([DB_FROM],7), [Forms]![Interest_Christine]![Principal]
ORDER BY Right([DB_FROM],7);
 
Hi,



If the interest is constant, initial_amount * ( 1+ term_interest) ^
number_of_terms is the required math.


SELECT a.clientID, a.date_time_stamp, SUM( b.amount * (1 +
b.month_interest) ^ dateDiff( "m", b.date_time_stamp, a.date_time_stamp ) )
FROM myTable As a LEFT JOIN myTable As b
ON a.clientID = b.clientID AND a.date_time_stamp > b.date_time_stamp
GROUP BY a.ClientID, a.date_time_stamp



can be a possible solution giving an history of each clientID account. Sure,
that does not take into account any payment, which is probably flaw, in
addition to assuming there is a constant interest rate.

If you have to consider payments and variable interests, I would suggest you
use a solution based on an ordered recordset, rather than on pure SQL (since
SQL will recompute from scratch each account, for each transaction in an
account, while an ordered recordset + VBA could remember the previous work
already done).


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top