Have a challenge 1

[pkdata]

I have 2 tables. One with full zipcodes. The other with an assigned 2 digit character with a zip5 and specific ranges of zip4's.
Example:
Table 1 titled Work contains zip 20171-1512
Table 2 titled Districts contains a zip4 range for each assigned store.
zip5=20171 zip4 start=1000 zip4 stop=1499 store=10
zip5=20171 zip4 start=1500 zip4 stop=1548 store=11

Obviously, this example would populate the store with 11, however I have over 55,000 records to populate.
What I need to do is populate the store field in the Work table with the associated store # from the Districts table when the Zip5's are the same the the zip4 falls with in the range in the Districts table.

I have been working on this and have run into a brick wall. Any ideas would be greatly appreciated.

 

[mm0000]

select 0
use work

select 0
use district
index on zip5 tag zip
set order to zip

select work 
locate
do while not eof()
   store substr(zip,1,5) to m.zip
   store val(substr(zip,7,4)) to m.zip4
   select district
   seek m.zip
   if found()
      scan while zip5 = m.zip
           if between(m.zip4,start,stop)
              store store to m.store
              select work
              replace store with m.store 
              exit
           endif
      endscan
   else 
      ?'Zip does not exist in District table'
      select work
   endif
   skip
enddo

Not tested.

 

[mm0000]

select 0
use work

select 0
use district
index on zip5 tag zip
set order to zip

select work 
locate
do while not eof()
   store substr(zip,1,5) to m.zip
   store val(substr(zip,7,4)) to m.zip4
   select district
   seek m.zip
   if found()
      scan while zip5 = m.zip
           if between(m.zip4,start,stop)  &&assuming start and stop numeric, else use val()
              store store to m.store
              select work
              replace store with m.store 
              exit
          endif
      endscan
   else 
      ?'Zip does not exist in District table'
      select work
   endif
   select work
   skip
enddo

 

[pkdata]

Worked great; thank you!!!!!!

 

[craigber]

Instead of

SELECT table
DO WHILE NOT EOF()
SELECT table
SKIP
ENDDO

use

SELECT table
SCAN
ENDSCAN

It's much faster

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[Mike Lewis]

I'd suggest that you change this:

 ?'Zip does not exist in District table'

to this:

 ? mZip + ' does not exist in District table'

That way, the user will know which zips don't exist. With your original code, they will simply see the same message N times, where N is the number of missing zips.

Going further, it might make sense to place these messages in a report, or at least a text file, so that the user can peruse them at his leisure. As things stand, you are simply displaying them on the background screen, where they risk scrolling off or being overwritten.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy