AnsweredAssumed Answered

Calculating money owed by client

Question asked by IlseVerhaert on Nov 22, 2013
Latest reply on Nov 25, 2013 by IlseVerhaert


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.

Table 1:
     - 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

Table 2:
     - 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!