Problem with continuous form

[ezybusy]

Hi everyone, Kindly have a look at the following relationship diagram






and tell me if it is possible to have a continuous form displaying as follows:

ID	DATE	CLIENT	QTY	PRICE	Amount Due	Total Paid	Balance
1	03/05	John	5	100	500	300	200
2	03/05	Alice	3	300	900	0	0
3	03/07	Alan	8	400	3200	0	0
4	03/08	Peter	3	200	600	400	200

Where order with ID 2 and 3 have not been paid yet (don't exist in OrdersPayments table).



EXPLANATIONS:

I actually want the continuous form to display the " ID - date - client - qty ordered - price - amount due - Total Paid - Balance to be paid " for all orders.

* The problem is, since some orders have not been paid yet (Don't exist in OrdersPayments table) i can't get all orders listed; which is normal.
So What i want to do is set the [total paid] and [balance to be paid] to zero (0) for those orders that are yet to be paid.

I have attached a sample db, kindly have a look at it to better understand what i mean.Orders List sample.zip

[CJ_London]

1. use a left join between orders and orderspayments instead of an inner join
2. you can then calculate your balance as

amountdue-nz(totalpaid,0)

[June7]

You receive multiple payments and associate them with specific order (as opposed to just client Account).

This sort of data summary is best done on report. Textbox on report has a RunningSum property.

[ezybusy]

Thank you Ajax. Worked