Okay I simplified it and I do get a result table but not exactly what I want. The result table only shows the last record for the invoice. I am going to work on it using the HAVING as a starting point but just to give you a better idea, the following example is what I am looking for.

Original Table

vendnum  invoicenum  type  tranamount
=======  ==========  ====  ==========
a1378       123       inv      231.00
a1378       123       pay     -231.00
a1378       456       inv      723.00
a1378       456       pay     -223.00
a1378       789       inv       56.00
a1378       891       inv      481.00
a1378       891       pay     -481.00

b4325       852       inv      542.00
b4325       852       pay     -542.00
b4325       891       inv      159.00

Resultant Table-

a1378       456       inv      723.00
a1378       456       pay     -223.00
a1378       789       inv       56.00

b4325       891       inv      159.00

As you can see I am eliminating fully paid invoices but keeping all the details of the others for display. And yes there can be two invoices with the same number for different Vendors and that has to be taken into account as well so somehow I eventually have to GROUP on a vend/invoice field.

See this is what happens when one is to fast on his delete finger or I wouldn't have lost the original solution. AARRRGH. Oh Well. Thanks again.


>>>Barbara
>>
>>Good thought but when I do that I get a "Subquery is invalid" error.
>
>Lets try to simplify it:
>

>CREATE SQL VIEW apinquiry AS ;
>select * from awards!apmaster, sum(apmaster.tranamount) as sumamount;
>  from apmaster ;
>  GROUP BY invoicenum, ORDER BY vendornum, invoicenum HAVING sumamount <> 0
>

>The HAVING clause says to just choose sumamount values that are <> 0, and the rest matches your specs I think.
>
>Barbara