You need to add to the test conditions a calculation of weither the value field is positive or negative; or create a new field in the source table with a positive / negative indicator and add that field to the test conditions
Rick Q On Mon, Jan 30, 2012 at 8:42 AM, Alan Bourke <alanpbou...@fastmail.fm>wrote: > > I have a DBF with invoice and credit note information. There can be one > or more records per invoice/c.note, the uniqueness is account code + > reference + date. So all rows with the same combination of those three > fields can be taken to encapsulate one invoice/c.note transaction. > > I need to verify that for a given transaction the value field in all the > rows belonging to it are either >0, and therefore an invoice, or <0 and > therefore a credit note. > > If there is a mixture of both <0 and >0 then this is a fail condition. > > It can obviously be done in a few lines of code but can it be done in > one SQL Select statement ? > > In the example below, the first three are one transaction which would > fail because one value is negative, the last two are taken as two > separate transactions and would be OK. > > > Account | Reference | Date | Value > --------+-----------+------------+---------- > AB001 | Ref 1 | 30/01/2011 | 39.99 > AB001 | Ref 1 | 30/01/2011 | 150.90 > AB001 | Ref 1 | 30/01/2011 | -93.99 > CD002 | Ref A | 15/09/2011 | -49.99 > CD002 | Ref B | 15/09/2011 | 22.99 > -- > Alan Bourke > alanpbourke (at) fastmail (dot) fm > > [excessive quoting removed by server] _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/CADaZP2n-t=a42+xsomgqz-vmxdobrocfv6dfpf13yllk3db...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
