How to replace limit amount with zero

[ping99]

Hi all,

I have mytable contains 3 fields

groupe 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 ? IF THE GROUP HAS MORE THAN ONE SINGLE
RECORD LIKE GROUP AAAAA AND GROUP DDDDD.

GROUP BBBBB, CCCCC, EEEEE AND FFFFF WILL STAY THE SAME
in mynewtable.

groupe 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 below.

Here is my codes: not work yet , please correct

insert into Tmpgroup
select groupe, count(*) as MyCount
from myTable group by groupe;

** this query is to create mynewtable :

select myTable.Groupe, iif(myTable.Option = "YES" and TmpGroup.MyCount > 1,;
LimitAmount, 0000) as LimitAmount;
from myTable inner join TmpGroup on myTable.Groupe = TmpGroup.Groupe;