How to calculate opening balance in a report

I have 3 tables

tblSales

invoice_no Customer_id date

001 222 12/3/04

002 222 12/4/05

003 222 12/6/05

tblSalesDetail

invoice_no product_id unit price qty

001 01 20$ 100

002 01 5$ 100

003 02 10$ 100

TblPayment

Invoice_no payed amount date

001 200 $ 12/4/04

002 400 $ 12/5/04

002 100 $ 30/5/04

003 400 $ 10/6/04

I want to build a report,that has the current inventory

detail and also the "oldbalance" or "opening balance"

opening balance =(total money payable)-(total money payed by the customer)

in this case it will be:

(value of invoice 001 +value of invoice 002) –(Money payed against invoice 001 and 002)

value of invoice 001 =20*100--X2000

value of invoice 002 = 5*100--X500

and

money payed against invoice 001 = 200

money payed against invoice 002 = 400

money payed against invoice 002 = 100

so now the opening balance =

(money payable) - (money payed)

(2000+500) - (200+400+100)

opening balance=2500-700

opening balance=1800

Model of the report:

Invoice no: 003

Customer id 222

Date 12/6/05

Product id unit_price quantity Price

02 10$ 100 1000

value of this invoice=1000

opening balance = 1800

total money payable =2800

money payed against this invoice = 400

closing balance =2400

Note: this closing balance will be the opening balance for the next invoice.

How do I calculate "opening balance"?

Thanks.

Yaqub ahmad.

Yaqub_ahmad.ms-access.itags.org.msn.com

This question is posted by yaqub ahmad belong to pakistan.

http://ms-access.itags.org/q_ms-access-database_95586.html