Calculating money owed by client
I have a rather complicated question, and I hope you can help me out. Let me briefly outline the context.
- Field: ClientID (text)
- Field: Date this client started to be a client (Date)
- Field: Payment plan (value list: "$30 - $15"; "$10-$10"; "0 - $10"). This payment plan works as the following: The first amount is an initial payment, when becoming client. The second payment is the monthly payment which the client needs to pay in the following months (indefinitely). e.g. $30 -$15 means an initial payment of $30, and thereafter $15 each month
- Field: ClientID (text) (yet related with table1)
- Field: Monthly invoice: How much should the client pay that month. This will depend on the start date and the payment plan. So, if Client X started this month and signed up for the payment plan $30-$15, this field should display $30. If Client X is client for already several months, this field should display $15. => how to calculate this?
- Field: How much money should the client have paid since his start until the current date. This should summarize his initial payment, and the following monthly payments. => how to calculate this?
I guess I should combine several functions for these calculations, such as the 'case-function'. Could someone help me out please? Thank you so much!