Penalties applied according to a table

[sargon]

I have three tables:

1.
tblRate - (in accordance with the law, penalty rates are applied at certain intervals)
- rate_ID
- Start_Date (as date)
- End_Date (as date)
- penalty_rate (%)

2.
tblCompany - (companies that have debts)
- Company_ID
- Company_Name
- Debt_Start (as date)
- Debt (money)

3.
tblPayments - (payments made by companies that reduce their debt, as much as they want, when they want)
- PaymentID
- Payment_Date (as date)
- Company (combobox)
- Amount (money)

Penalties are applied to the total debt, starting with Start_Date. They are applied according to the rate established by law for the compensatory interval (tblRate).
The debt is reduced according to each payment, but penalties are also applied to the final debt.


Example, for company Alpha (companyID = 2)

 

[sargon]

My database and excel test... I cannot arrange the order of the L_Date correctly

 

[arnelgp]

do you think there is a missing line on your excel sheet.
here is what i get:

 

[sargon]

I don't think so, because nothing changed in that interval, there was no change in the rate and no payment. E_data should be descending and in this example, unique.

At 222 line E_data should be 10/01/2024.

 

[arnelgp]

i am using Temp table (Computed) for output.
see the (long) VBA code on Module1.
Open form1 for the result (or Query1).

 

[sargon]

Thank you! It is a big help. It can be done without table Computed? All the data are allready in the other three tables (Company, Rate, Payment)...

 

[arnelgp]

at the moment, I can only think of VBA and Temp table to accomplish this.