Thread: Dynamic Information
10-16-13, 16:35 #1Registered User
- 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.
10-17-13, 09:48 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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 basisI'd rather be riding on the Tiger 800 or the Norton