I do collection work for creditors in the UK who have clients in the US.
I have a database where the debtor has been invoiced in £GBP which I convert to $USD on the day of invoicing. When we receive payments from the debtor I need to convert it to £GBP based on the conversion rate that day. In addition I have to track interest due on the account. Presently the currency conversion and interest calculation is being done in an Excel spreadsheet and then entered back in Access.
Is there a way to design a query that will do an accrued interest calculation which is based on the intrerest rate, date of last payment to the date of current payment