Question Trying to do a summary bank statement for calculating interest

Hi all,

I am trying to get a specific output and I don't know whether I should do it in a query or in a report, hence I post in General.

My problem is the following: In my database, some tables store financial data in the following way: "Project", "Transaction Date", "Amount" (with a few other fields irrelevant here). I want to calculate interest earned on specific projects, on a quarterly basis. Each quarter I know the interest rates for the last 3 months, so for instance from 1 January 2009 to 23 February 2009 it is 1.05%, from 23 February to 4 March it is 0.75%, etc.

Then I need to allocate it to projects, based on the balance for each project at a given date. The way it was done so far (owing to the poor database design before, and my limited Access skills!), was to create a "bank statement" for each project, i.e. a simple chronological list of transactions, and in the report I would add a "running balance". Then I use this running balance to attribute interest.

However this isn't very easy to use as I have to export this into Excel and manually calculate interest for each date, and calculate how many days there have been with this balance. A bit slow!

I was thinking that I could simplify it in two ways:
- Either design a whole form in which I would input the interest rates and the associated dates, and then work out interest. However I have no idea how to do that...
- Or create a "summary" bank statement, which would only show the dates at which the balance changes. The format could be, for each project:
"Date", "Balance", "Number of days with this balance".
But what I have seen from reports and queries haven't given me a clue as to how to calculate this.

Any ideas or leads?
Thank you!
Rémi

This would be my first take on a solution to a similar, but much simpler problem. Others with more experience probably have better solutions.

Open the VolumesForm, change some values, and press the button. Examine its OnClick event to see what is happening. This should help you understand what is needed to eliminate your 'Excel' step.

The cumulative total could go into the main database, so you only operate upon one recordset if you want. Choose the simplest solution to your own problem. That may involve multiple tables, or it may only use one table depending upon your requirements.

I hope this helps.
David

ps - I see that one of the golden rules is to never store a calculated value. My example shows calculations being done using a scratch table. I'm curious how others would eliminate that table. In the case of a full-scale solution to your problem, where payments and subtractions can be made any day of the year, and the interest is accrued daily, my gut tells me that a scratch table would be the way to go. My first thought is also to store the balance on a quarterly basis in a table somewhere. If you have twenty years of data, is it really necessary to recalculate everything from day one of that data? I'm curious what the pros say. Do bank statements recalculate everything from the time you opened your account, or do they generally store a balance somewhere when a statement is ran, so that that value can just be looked up to use as a beginning balance on the next statement?

Thank you for this example!

I will see whether that can be the way forward. I admit that re-calcuating all cumulative balances every time is a bit cumbersome, but then I only need to it quarterly so maybe it is fine.

Thanks,
Rémi