I HAVE 2 TABLES AND ITS COLUMN LISTED:

  TABLE INV: [INVOICE NUMBER], [CUSTOMER NUMBER], AMOUNT  
  TABLE INV_payment: [INVOICE NUMBER], [CUSTOMER NUMBER],CHECKNUM,CHECKAMOUNT

BOTH TABLE JOIN BY: [INVOICE NUMBER], AND [CUSTOMER NUMBER]  
NOTES: table inv contain all invoices of  a customer.
       table inv_payment contains  all invoice payment. and one invoice can be 
       pay by multiple checks.
 
To find out what a customer balance and his/her activity , my query is
as following:

   SELECT Inv.CustomerNumber, Inv.Invoice_NUMBER, INV.InvoiceDate,  
         Inv.InvoiceAmount,  INV_PAYMENT.CheckNumber,  INV_PAYMENT.CheckAmount, 
         Inv.InvoiceAmount-INV_PAYMENT.CHECKAMOUNT AS BALANCE_owned
FROM INV LEFT JOIN INV_PAYMENT ON (INV.CustomerNumber =
INV_PAYMENT.CustomerNumber) AND (INV.Invoice = INV_PAYMENT.Invoice)
AND INV.Customer = xxxxxx

PROBLEM: this query only work if the invoice is paid by one check
only. If an invoice of a customer is paid by multiple checks the
balance amount will not be correct.  I am interested a "simple" sql
statement which will show the invoice/check activity and the correct
roll up balance for this situation. Thank you in advance for any pointers you
may have.

---

Clarification of Question by accessnewbie-ga on 29 May 2006 15:00 PDT

aidan321, thank you for pointing out the problems in my syntax.  I
think your susgestion should give me almost the output i want ... i
will try it ..

Just to clarify my question a little more by an example

    table inv has:                     
        customer                      Invoice   
        number     Invoice Number     Amount
       ----------  ----------------  -----------
        100            9997           150.00
        100            9998           125.00
        100            9999           100.00

    table inv_payment has: 

	Customer     Invoice Number     Check Number    Check Amount  
	--------     --------------     ------------     -----------
	100	        9998     	    2               100
	100	        9998                3                25 
	100	        9999                4	     	     10
	100	        9999                5		     20
	100	        9999                6		     25  

  I hope to have a sql statement to produce something like bellow
listing:
                                      
Customer   Invoice    Invoice    Check      Check    Invoice  
Number    Number      Amount     Number     Amount   Balance 
------    --------   ---------   --------   ------   --------
 100        9997       150                            150  
  -         9998       125         2          100     125  
  -           -         -          3           25       0    
  -         9999       100         4           10      90
  -           -        -           5           20      70  
  -           -        -           6           25      45

Your select statement references fields for date and does some
renaming, which makes it difficult to write a precise query.  However,
there are two key problems with your approach.

Firstly, you will find it difficult to do a summation of total paid
for a given customer and invoice in the same query.  The easiest way
arround this is to build two seperate queries: one that summarizes
total payments by invoice and customer number, and a second that uses
the resut of the first query to reduce the original invoice payment.

So, using ONLY the fields listed above (add or rename as needed, it
will not affect the logic as long as you do not change your join
properties).

Query1 - saved as "first": 
SELECT INV.[CUSTOMER NUMBER], INV.[INVOICE NUMBER], INV.AMOUNT,
Sum(INV_PAYMENT.CHECKAMOUNT) AS [Total Paid]
FROM INV LEFT JOIN INV_PAYMENT ON (INV.[CUSTOMER NUMBER] =
INV_PAYMENT.[CUSTOMER NUMBER]) AND (INV.[INVOICE NUMBER] =
INV_PAYMENT.[INVOICE NUMBER])
GROUP BY INV.[CUSTOMER NUMBER], INV.[INVOICE NUMBER], INV.AMOUNT;

Query2:

SELECT first.[CUSTOMER NUMBER], first.[INVOICE NUMBER], first.AMOUNT,
first.[Total Paid], [AMOUNT]-[Total Paid] AS Remainder
FROM [first];

Hope this will help! If you provide a complete list of the starting
fields in the tables and the exact output fields you want, the
researcher will be able to write the exact SQL syntax you require much
more easily.