I have several tables, the tables in question and needs attention as follows:
1- tblContract (PK Contract_Ref_ID)
2- tblSchdule (FK Contract_Ref_ID) : Table contains Fields (Contract_REF_ID, PaymentNumber, DueDate, AmountDue ... and other fields)
3- tblPayment (FK Contract_Ref_ID) : Table contains all payment made by client such as (Rent, Insurance, Utilities...etc)m and this table having the field (Contract_REF_ID, PaidDate, PaidAmount , PayType .. and other fields)
I am having an issue to build an account statements (Similar to Bank Statement Account) that includes the following fields and demonstrated as follows:
Date Credit Debit Balance
11/01/2015 0.00 11.11 -11.11 (Debit Side always from tblSchdule)
12/01/2015 11.11 0.00 0.00 (Credit Side always from tblPayment)
15/01/2015 55.55 0.00 55.55
11/02/2015 0.00 11.11 44.44
I would greatly appreciate your help to solve this issue.
In advance Thanks a millions.