How to calculate opening balance in a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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----------2000
value of invoice 002 = 5*100----------500
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.
(e-mail address removed)
 
Back
Top