Multiple loan database design

[Julieve]

I need to create a database of transactions to track loans between different entities, eg. Business A loans $10,000 to Business B.. and Business A also loans $5,000 to Business C, when Business B repays part of the loan to Business A, we want to be able to calculate interest on the funds borrowed for the number of days borrowed and add that the funds repaid. There are going to flows of funds between businesses constantly so will also want to create a report that shows the transactions for each separate loan and a list of the balances owed between the businesses.. It sounds simple but trying to design it is getting confusing

As a starting point I was trying to do the transaction with two lookup fields accessing a list of Entity Names, but realised I wouldnt be able to show the transaction amounts for each part to the transaction.. Should I have one table line for Business loaning the money and a separate transaction for Business B borrowing the money..

Interested to get feedback.. or suggestion as to where someone else may have already tackled this problem.

[CJ_London]

Should I have one table line for Business loaning the money and a separate transaction for Business B borrowing the money..

Danger with that way is for some reason only one side of the transaction is shown, the other perhaps lost due to powercut, break in network service etc. If you go that route, google the vba functions 'begintrans' and 'commitrans'.

Or you can do it with lender and borrower in same record in much the same way as you would for an accounting solution. However you seemed to have dismissed this method because

realised I wouldnt be able to show the transaction amounts for each part to the transaction

and I don't understand what this means

[Julieve]

After setting up the transaction table entry with both a borrower and lender code (as well as date, amount, description etc) - I then wasnt sure how to get a query to find the transactions between the borrower and lender and vice versa as the loan is repaid. I was going to have one table for transactions, and perhaps two forms, one for the lending transaction and one for the repayment, that could show the list of borrowed funds.

[CJ_London]

you would have a further records where the transaction goes the other way. something like


PK..........Lender......Borrower.........Date..... ...Amount...Description
1............ABC Ltd....XYZ ltd............1/1/2016. 2000.00 Loan
2............ABC Ltd....XYZ ltd............1/2/2016. -500.00 Repayment
3............ABC Ltd....XYZ ltd............1/3/2016. -500.00 Repayment

[Julieve]

Yes, that was the format of my transaction table..

I had some great help in a thread on another post.. where they suggested having a PartyType and linking my two parties to a LoanID and it works really well..

https://www.accessforums.net/showthread.php?t=24778

So have solved the first part of my problem...

Now I have another part of the problem

have a transaction table of entries of different loans and want to show a running balance and calculate interest based on those entries, less any repayments and ignoring any amounts that have had interest charged on them before.. We will probably need to keep changing the interest rate as well

So i would imagine I need a table of


tbl_IntRates
LoanID
IntRate
IntStartDate


tbl_LoanInt
LoanID
DateIntCharged
IntAmount



However, first I have to calculate a running balance to calculate the interest based on a formula I guess I put into the report, then how do I get that calculation into my table of LoanInt

[CJ_London]

see this thread re running balance

https://www.accessforums.net/showthr...825#post328825

which asks the same question

with regards loan interest, you need to be much clearer about the calculation required - is it calculated daily (so interest is charged on interest), monthly etc