Hmmm, perhaps a GROUP BY, including a field like count(*) as MyCount. Join the result with the original table, so that you can include MyCount in the condition.

>Thanks Hilmar for your prompt reply,
>
>The condition is by group, you see in GROUP AAAAA has 4 records, 3 records with option = NO needed to replace immediately with zero.
>
>but for a single group like GROUP BBBBB , CCCCC, EEEEE and FFFFF all records will stay the same no change even option = NO .
>
>but be carefull GROUP DDDDD needed to change a 2nd record to become zero.
>
>means every group that has than more than 1 record must be changed.
>
>Hope this clarify.
>
>Thanks again
>Paul
>
>Hilmar wrote :
>
>select group, iif(SomeCondition, LimitAmount, 0000) as LimitAmount,;
> Option;
> from Table1;
> into table Table2 && or Into Cursor Temp
>You must replace the condition; I didn't quite understand what condition you want.
>
>The "0000" is to make sure the field has at least 4 digits. Otherwise, data can be truncated.
>
>>Hi all,
>>
>>I have mytable.dbf contains 3 fields
>>
>>group limitamount option
>>
>>AAAAA 1000 YES
>>AAAAA 2000 NO
>>AAAAA 3000 NO
>>AAAAA 4000 NO
>>
>>BBBBB 5000 NO
>>CCCCC 6000 NO
>>
>>DDDDD 7000 YES
>>DDDDD 8000 NO
>>
>>EEEEE 9000 NO
>>FFFFF 2000 NO
>>
>>How to do select SQL statments in order to have mynewtable.dbf ?
>>
>>group limitamount option
>>
>>AAAAA 1000 YES
>>AAAAA 0 NO
>>AAAAA 0 NO
>>AAAAA 0 NO
>>
>>BBBBB 5000 NO
>>CCCCC 6000 NO
>>
>>DDDDD 7000 YES
>>DDDDD 0 NO
>>
>>EEEEE 9000 NO
>>FFFFF 2000 NO
>>
>>In this above example, we need to replace in group AAAAA 3 records with option = NO and group DDDDD 1 record with option = NO to become zero.
>>
>>TIA for any help in coding in SQL statments.