No, it's not a good approach - sorry. If you need to update YTD field for all months, then use the idea from my other message (just one simple scan).
Once again - how do you match the records by month? Is Name + Month combination unique? And each Name repeats for each month?
>thank you very much,
>
>
>but i hope to help me again if you have a time.
>it works.
>i try as below i hope this help to rewrit with new code
>
>SELECT ID1 ,tY2010, cast(0 as int) as Row FROM NEWTEST WHERE ID1=2 AND ID>0 into cursor crs1 readwrite
>replace all Row with recno() in crs1
>brow
>SELECT ID1 ,Y2010, cast(0 as int) as Row FROM NEWTEST WHERE ID1=3 AND ID>0 into cursor crs2 readwrite
>replace all Row with recno() in crs2
>************************************************************
>
> select c2.ID1, c2.Y2010 + c1.tY2010 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
>
>
>local lnRecNo
>lnRecno = 1
>select NewTest
>scan for ID1 = 3
>if between(m.lnRecno, 1, reccount('crsResult'))
>
> goto lnRecno in crsResult
> replace ty2010 with crsResult.Total
> endif
> lnRecno = lnRecno + 1
>endscan
>>>Sorry, if you want to go this route, you may try it yourself.
>>
>>Still, how exactly do you want to match records for each month? The Row approach I took is wrong, because I didn't use any order.
>>
>>>thank you for idea,
>>>this code work for id1=1 and id1=2
>>>
>>>SELECT ID1 ,Y2010, cast(0 as int) as Row FROM NEWTEST WHERE ID1=1 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=2 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
>>>
>>>
>>>you can help me to
>>>make another 2 select as
>>>if id1=3
>>>select id1,ty2010 wher id1=2
>>>
>>>SELECT ID1 ,Y2010, cast(0 as int) as Row FROM NEWTEST WHERE ID1=3 AND ID>0 into cursor crs2 readwrite
>>>replace all Row with recno() in crs2
>>>
>>>step3
>>>select and sum from the last steps and replace sum with ty2010 where id1=3
>>>
>>>
>>>>Ok, here is a quick idea - you need to take it and adjust.
>>>>
>>>>create cursor crsCummulative(Month N(2), Name C(10), YTD N(20,3))
>>>>
>>>>index on str(Month) + Name tag MonthName
>>>>
>>>>select NewTest
>>>>set order to MonthName
>>>>scan while not eof()
>>>> if seek(str(ID1) + Nam, 'csrCummulative', 'MonthName')
>>>> replace YTD with crsCummulative.YTD + NewTest.y2010 in crsCummulative
>>>> else
>>>> insert into crsCummulative values (NewTest.ID1, NewTest.Nam, NewTest.y2010)
>>>> endif
>>>> replace TY2010 with crsCummulative.YTD
>>>>endscan
>>>>
>>>>
>>>>>thank you for reply
>>>>>may be a one scan solution will be better?? how this is the qustion
>>>>>
>>>>>i need to update just the newtest as what i ask before, thank you for code
>>>>>
>>>>>
>>>>>m.tnNumber1=1
>>>>> m.tnNumber2=2
>>>>> m.tnNumber2=3
>>>>>.
>>>>>.
>>>>>.
>>>>> m.tnNumber2=12
>>>>>
>>>>> 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
>>>>>
>>>>>*****************************************if id1=3
>>>>>
>>>>>
>>>>> SELECT ID1 ,Y2010 FROM NEWTEST WHERE ID1=m.tnNumber3 AND ID>0
>>>>>
>>>>>id1 y2010
>>>>> 3 4
>>>>> 3 3
>>>>> 3 6
>>>>>
>>>>>now
>>>>>id1 ty2010
>>>>>3 27.3
>>>>>3 34.5
>>>>>3 39
>>>>>
>>>>>.
>>>>>.
>>>>>.
>>>>>.
>>>>>id1 ty2010
>>>>>12 12
>>>>>12
>>>>>
>>>>>
>>>>>
>>>>>>I understand, that it may work, but you don't need to use scan/endscan in that code at all. Removing scan/endscan should produce the same result.
>>>>>>
>>>>>>Anyway, use the idea from my last post to do the same for your NewTest. I'm not sure what is GroupId field in your case, it may be Nam field.
>>>>>>
>>>>>>Also, be aware that performance may suffer with this solution - may be a one scan solution will be better.
>>>>>>
>>>>>>>hi,
>>>>>>>Sorry the last code i send it works ,
>>>>>>>
>>>>>>>i want to show how i get values monthly and replace ment at newtest.
>>>>>>>
>>>>>>>
>>>>>>>i need to update just the newtest as what i ask before, thank you for code
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>Sorry - it looks like you don't need to use SCAN in that code, but anyway, you lost me.
>>>>>>>>
>>>>>>>>Let's assume this structure
>>>>>>>>
>>>>>>>>GroupID Month Value YTD (values we want to have)
>>>>>>>>1 1 10 10
>>>>>>>>2 1 15 15
>>>>>>>>1 2 20 30
>>>>>>>>2 2 5 20
>>>>>>>>
>>>>>>>>
>>>>>>>>select T1.GroupID, (select sum(Value) from myTable T2 where T2.GroupID = T1.GroupID and T2.Month <=T1.Month ) as YTD
>>>>>>>>from myTable T1
>>>>>>>>
>>>>>>>>
>>>>>>>>>hi,
>>>>>>>>>
>>>>>>>>>evrye values for filelds partofname is 2009 it is old values just i used to compare ,
>>>>>>>>>
>>>>>>>>>but every month i fill(replace) the newtest form another table have 6 values via this code
>>>>>>>>>
>>>>>>>>>SET DELETED ON
>>>>>>>>>USE newtest
>>>>>>>>>UPDATE newtest SET y2010 = T.val1 from newtest inner join hamad T on newtest.ID = T.ID WHERE id1=2
>>>>>>>>>
>>>>>>>>>UPDATE newtest SET PER1= (Y2010-Y2009)/Y2009 from newtest WHERE ID1=2
>>>>>>>>>*brow
>>>>>>>>>
>>>>>>>>>***********************************************************************
>>>>>>>>>scan
>>>>>>>>>
>>>>>>>>>SET FILTER TO ID1=2 AND FLG=1
>>>>>>>>> SUM Y2010 TO M.D FOR id1=2
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>SET FILTER TO
>>>>>>>>> REPLACE Y2010 WITH M.D FOR NAM="TOT1" AND id1=2
>>>>>>>>>
>>>>>>>>>endscan
>>>>>>>>>
>>>>>>>>>scan
>>>>>>>>>
>>>>>>>>>SET FILTER TO ID1=2 AND FLG=2
>>>>>>>>> SUM Y2010 TO M.D FOR id1=2
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>SET FILTER TO
>>>>>>>>> REPLACE Y2010 WITH M.D FOR NAM="TOT2" AND id1=2
>>>>>>>>> endscan
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>scan
>>>>>>>>>
>>>>>>>>>SET FILTER TO ID1=2 AND FLG=3
>>>>>>>>> SUM Y2010 TO M.D FOR id1=2
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>SET FILTER TO
>>>>>>>>> REPLACE Y2010 WITH M.D FOR NAM="TOT3" AND id1=2
>>>>>>>>> endscan
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>>The output above was not really helpful. Do you match records by name? In other words, for each month you have many records. How do you match each month?
>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>>Field Field Name Type Width Dec Index Collate Nulls Next Step
>>>>>>>>>>>>> 1 ID1 Numeric 3 No
>>>>>>>>>>>>> 2 ID Numeric 2 No
>>>>>>>>>>>>> 3 NAM Character 10 No
>>>>>>>>>>>>> 4 Y2009 Numeric 15 3 No
>>>>>>>>>>>>> 5 Y2010 Numeric 15 3 No
>>>>>>>>>>>>> 6 PER1 Numeric 15 3 No
>>>>>>>>>>>>> 7 TY2009 Numeric 15 3 No
>>>>>>>>>>>>> 8 TY2010 Numeric 15 3 No
>>>>>>>>>>>>> 9 TPER Numeric 15 3 No
>>>>>>>>>>>>> 10 FLG Numeric 2 No
>>>>>>>>>>>>>** Total ** 108
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>Ok, and also some data (copy to 'temp.txt' delimited with tab)
If it's not broken, fix it until it is.
My Blog 
