We help IT Professionals succeed at work.

ms access Account Statement Balance

TAB-000
TAB-000 asked
on
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.
Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2013
Commented:
Try this -

SELECT DueDate, PaidDate, NZ(AmountDue, 0)  AS Debit, NZ(PaidAmount ,0) AS Credit,   NZ(PaidAmount ,0)  - NZ(AmountDue, 0)   AS Balance
FROM  (tblContract c LEFT JOIN tblSchdule s ON c.Contract_Ref_ID = s.Contract_Ref_ID) LEFT JOIN tblPayment p ON p.Contract_Ref_ID = c.Contract_Ref_ID

Open in new window

Author

Commented:
Dear mbizup,

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

Author

Commented:
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.