>Hi all,
>
>I'm trying to search a table for 'missing' records. I want to scan through my 'agency' table for agency i.d. codes, then search another table (newinit in this case) to see if there is a record for agency code (field=acode) = the value found in the scan of the agency table.
>
>I'm using the following code to do this (VFP5.0), but I'm coming up with an endless loop in the DO WHILE section in which I'm getting 'millions' of records with acode=myscanvalue, but a new ntid (my key field formy newinit table) and I have to cancel the program.
>
>Any clue what I'm doing wrong here?
>
>*****
>USE newinit
>USE agency in 0
>SELECT agency
>SET ORDER TO acode
>GO TOP
>SCAN
> myscanvalue=alltrim(acode)
> SELECT newinit
> LOCATE FOR alltrim(acode) = myscanvalue
> DO WHILE !FOUND( )
> SET ORDER TO ntid
> GO BOTTOM
> x=ntid
> x='00'+alltrim(str(val(x)+1))
> APPEND BLANK
> REPLACE acode with myscanvalue
> REPLACE ntid with x
> ENDDO
> SELECT agency
>ENDSCAN
>********
>
>MTIA!!
>
>Mandy

Mandy,
Just curious, did you try using the select command to retrieve records that did not match your criteria and appending the resulting cursor into your table?

Eg: Select acode, '00'+alltrim(str(val(ntid)+1)) as ntid from agency where alltrim(acode) not in (select acode from newinit) into cursor one

select one
store dbf() to one_dbf
select newinit
append from (one_dbf)


Just a little idea, that's all......

Later,
Binoy Warrier