ms access Account Statement Balance
Dear All,
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
... etc
I would greatly appreciate your help to solve this issue.
In advance Thanks a millions.
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
... etc
I would greatly appreciate your help to solve this issue.
In advance Thanks a millions.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dear Jim Horn
As a new user into ExperExchange, this is first time I know it, Thanks for pointing.
Rest assured, I will take into consideration in the future post.
Best regards.
As a new user into ExperExchange, this is first time I know it, Thanks for pointing.
Rest assured, I will take into consideration in the future post.
Best regards.
ASKER
Many Thanks for your participation and cooperation.
Having apply and try provided code, the results showing a duplication for each records. For your easy reference, the attached image illustrates the sample records from both tables and the result of provided code.
Thanks and regards
1-tblScheduleT-SampleData.jpg
2-tblPayment-SampleData.jpg
3-Code-Results.jpg