Help with interest calculations

I wonder if anyone can help me with a specific calculation and put me in

the right direction.

I have to make capital and interest calculations on Credit and Debit

account movements, calculating the interest on the number of days that have

occured since the last calculated balance, and the current date (or a

monthly/bimonthly report). I know how to do this in excel but I don't know

how you make two dimensional calculations across registers in an Acess

Dynaset. I am learning Visual Basic for Apps but am a long way off from

getting this one resolved without help.

If you can help, below follow more details on the Table Relations and key

fields.

Thanks in advance.

Robin

Details:

I have a table called ProjectLoans and another one called Beneficiaries.

The two tables have a many to many relation given that a loan can have

several beneficiaries and vice versa.

The linking table called LoanDetails.

We wish to control the balance on Capital and Interests, calculating the

difference in the number of days that have passed since the loan was made

and todays date, taking into considerations any other movements in the

account and the date of these transactions.

Furthermore, as in the case of the shared loan, the truth is that each

individual tends to pay on different days. What we really want is to track

movements for each "subloan", ie for each record in the linking table, and

that way we can then obtain balance totals for each beneficiary across

various loans they might have and also totals for each loan project, or

groups of loan projects that might be funded by different financial

agencies. Hence I believe I should relate the Movements table that records

Date, Credit and Debit, to the linking table Loan Details. - Is this

correct?

Anyway, having got the records in a movements table how do I calculate the

Interest and balance.

Example:

Date In Out

1 March 1999 $300 $0

10 June 1999 $0 $50

15 Dec 1999 $0 $25

Today() $0 $0

On this data, for each record I need to calculate the subtotal between

debit and credit, the accumulated interest based on X% interest rate and

the number of days passed since the previous record/365 days in the year,

and the total balance outstanding (ie the outstanding capital +

accumulated interest.

Thanks alot if you got this far to read this.

Robin