Can anyone help. In the vtrans table there's distinct records, in the test_ann table, there are multiple records with teh same id (which is okay) if a record is found in vtrans for the first time, it just updates fields. if it scan throught the test_ann table and finds another instance of the record, then it should add that new one...but after a couple of records, it bombs...acan someone help? or tell me a better way?

sele vtrans
go top
scan
sele test_ann
go top
locate for allt(test_ann.id) == allt(vtrans.id)
do while found()
DO CASE
case vtrans.annual != 1
replace VTRANS.annual with 1
if TEST_ANN.passed = .t.
replace vtrans.visdate with TEST_ANN.visdate
replace vtrans.year with year(TEST_ANN.visdate)
replace vtrans.mindate with TEST_ANN.visdate - 30
replace vtrans.maxdate with TEST_ANN.visdate + 30
replace vtrans.formdate with TEST_ANN.formdate
replace vtrans.date_val with TEST_ANN.date_val
endif

case vtrans.annual = 1
insert into vtrans (id, udc_id, htcno, annual, regist, visdate, year, mindate, maxdate, formdate, date_val) values ;
(test_ann.id, test_ann.id,VAL(LEFT(test_ann.Id,3)),1,1,iif(TEST_ANN.passed = .t., TEST_ANN.visdate, {//}), iif(TEST_ANN.passed = .t., year(TEST_ANN.visdate), {//}), ;
iif(TEST_ANN.passed = .t., TEST_ANN.visdate - 30,{//}), iif(TEST_ANN.passed = .t., TEST_ANN.visdate + 30, {//}), ;
iif(TEST_ANN.passed = .t., TEST_ANN.formdate, {//}), iif(TEST_ANN.passed = .t., TEST_ANN.date_val, {//}) )

ENDCASE
CONTINUE
ENDDO

sele vtrans
endscan