Hi Mohammed,
Please re-read my reply again. I changed my solution after I re-read your requirement. I now do this with 3 separate cursors.
>thank you for reply,
>it is one table,
>i try but i get error message syntax error at first lin
>update newtest set ty2010 from newtest,&&syntax error
>(select sum(iif(ID1 = 1 and ID>0, Y2010,0)) + sum(iif(ID1 = 2 and ID>0, Y2010,0) as TotalSum from newtest)
>
>>>hi all,
>>>
>>>i need help to select twice from the same table and make sum to replacement to another fileld at the same table as below
>>>
>>>m.tnNumber1=1
>>> m.tnNumber2=2
>>>
>>> SELECT ID1 ,Y2010 FROM NEWTEST WHERE ID1=m.tnNumber1 AND ID>0
>>>id1 y2010
>>> 1 10.3
>>> 1 20
>>> 1 15
>>>
>>> SELECT ID1 ,Y2010 FROM NEWTEST WHERE ID1=m.tnNumber2 AND ID>0
>>>
>>>id1 y2010
>>> 2 12
>>> 2 11.5
>>> 2 18
>>>
>>>i need to sum y2010 where ID1=m.tnNumber1 + y2010 where ID1=m.tnNumber2 and replace at the same table to field ty2010
>>>
>>>id1 ty2010
>>>2 22.3
>>>2 31.5
>>>2 33
>>>
>>
>>
>>SELECT ID1 ,Y2010, cast(0 as int) as Row FROM NEWTEST WHERE ID1=m.tnNumber1 AND ID>0 into cursor crs1 readwrite
>>replace all Row with recno() in crs1
>>
>>SELECT ID1 ,Y2010, cast(0 as int) as Row FROM NEWTEST WHERE ID1=m.tnNumber1 AND ID>0 into cursor crs2 readwrite
>>replace all Row with recno() in crs2
>>
>>select c2.ID1, c2.Y2010 + c1.Y2010 as Total from crs2 c2 ;
>>inner join crs1 c1 on c2.Row = c1.Row into cursor crsResult nofilter
>>
>>update NewTest set ty2010 = c1.Total ;
>>from NewTest inner join crsResult c1 on NewTest.ID1 = c1.ID1
If it's not broken, fix it until it is.
My Blog