Larry,
I haven't forgotten. I just haven't had time to look at this with any depth.

Off the top of my head, it likes you will need two or three intermediate queries to create the necessary baseline to get the data you need. Do you have anything running now or is this a new requirement? I'll be able to look at it closer this weekend if you're still interested.

>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