Larry - thanks for your help anyway.

ANother question if I may:
I have a file, and in this file we have a transaction number, a description, a pay date, amount and amount paid .

Assuming the following:
Trans.# Description Pay date Amount Amnt. Paid
1234 Job 1 01/01/2001 $20.00 $20.00
1234 Advance 12/01/2000 -$20.00 -$20.00
1234 Job 1 02/01/2001 $30.00 $30.00
1234 Job 1 03/01/2001 $30.00 $30.00
4567 Advance 02/01/2001 -$50.00 -$50.00
4567 Job 2 02/01/2001 $10.00 $10.00

We need to create a report that will display the following:
For Salesman Johhny
For the date range 02/01/2001 to 02/10/2001

Trans.# Description Advance Amnt. Amount Paid to salesman Gross Amount
1234 Job 1 -$20.00 $30.00 $30.00
4567 Job 2 -%50.00 -$40.00 $0.00
Total Amount Due to Johhny $30.00

The big issue, is that the user will be applying a date filter. example:
Pay date = 02/01/2001 .
1) We need to for each transaction, Query1, sum the total amount paid excluding the Advance, using the the filter, and then
2) We need to for each transaction, found in the query1, sum the total amount paid excluding the Advance, ignoring the filter, (creating query2) and then
3) sum the total Advance for each transaction, found in query1, ignoring the filter. (Creating Query3)
4) Subtract the one sum from the other to see if there is a positive amount owing .
5) Finaly, update query1, with an advance total if any, subtract the advance from the amount paid as reflected in query1, and lastly for each transaction create a gross amount for only the values which do not reflect a negative amount.

In simple terms: A salesman gets an advance on each transaction. The salesman can be paid multiple times on a common transaction. The report needs to reflect what is owed to the salesman on each transaction for a given date range. The kicker here is, the date filter.

I hope I made sense.

Thanks,

Larry