p.s. Database is attached and pdf file of how query QTrans should look like after your help!
I have 3 Tables – One named TClients, another TAccounts and another TTrans (Transactions).
The TTrans (Transactions) table consists of the following fields: TrID (AutoNumber), AccID (Reference to Account Number in TAccounts Table), Int = Interest Rate (percentage), TrDate (Transaction date) Debit and Credit fields (both set as Number).
I’d like a QTrans to pull data from the TTrans (Transactions) and insert the following 3 calculated fields:
1. ‘Balance’ field for each account = Running sum on ‘Total’, but for each account number and on last date occurrence in case of duplicate dates;
2. ‘Days’ field to calculate the difference in days between each transaction on the same account number- to show adjacent to ‘Balance’ field.
3. ‘Interest’ field to calculate accrued interest – adjacent to where Days field is not null.
Any help is much appreciated