Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2013

    Unanswered: Dynamic Information

    I have 3 tables: External Rate table (standard for all accounts), Internal Rate (varies between accounts) table, & Account Balance table. From all of these I created a query to calculate the interest (Interest Query): External rate + Internal rate. Now, my problem is when my internal rate. What I did is I created a query 1 to get the latest rate (max) and then created query 2 to use this information to calculate my interest. Having said that, I still have issues when my internal rate changes in a middle of the month. For example:
    Account #123; Internal Rate 5%; Date Jan 1. Then it changes to 6% on Jan 15. My internal rate table now will have these 2 records. When I run my Interest Calculation query, it would look for the last record which is 6% and apply to all calculations. Is there a way that access would recognize that only on balances on/after Jan 15 it should apply 6% and the rest is still 5%?
    Any information would be greatly appreciated.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    calculating interest is quite complex and dependingn on your data model it can be horrendous

    depedns on what yu are trying to achieve...
    is your interest calcuation run daily, monthly

    the main issue is that the principal can vary AND the rate can vary. matching the correct right to the proportion of the balance is tricky
    it also depends if you are rolling up the interest or calculating it at period end

    you'd probably be better off doing this as a stored procedure or possibly an overnight basis
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts