Zip Code Ranges to single zip w/pcode

[mttorpy]

I have seven original columns have to work with for solving this problem.

For every range of zip codes, I would like to break them up into single records, the problem being I have 3156 records and the ranges vary from 1 to 1000 zip codes per range.

So, I need to construct a VB script or SQL in that with create an additional record for every zip in the range and keep all information to the left of Zip1 the same for the additional records in that range.

Obviously I would no longer have a need for the zip2 column since all the zip code ranges would be broken down with corresponding pcodes.

Any ideas?

Thanks

Here is an example of two records, after the split I would need three more records created.

pcode bit Counrty state city zip1 zip2
300 0 usa al prat 36066 36068


pcode bit Counrty state city zip1 zip2
1100 0 usa mt prat 36532 36533

 

[PHV]

What have you so far ?
Assuming you have created a new table, I suggest playing with 2 recordset: (pseudocode)
While Not rs1.EOF
For z = rs1("zip1") To rs1("zip2")
rs2.AddNew
For i = 1 To 5
rs2(i) = rs1(i)
Next
rs2(6) = z
rs2.Update
Next
rs1.MoveNext
Wend

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mttorpy]

PHV-
That is a great start for me thanks!!! I am super new with VB what function does "For i = 1 To 5" play is that say the first 5 coulums = i?

 

[PHV]

For i = 1 To 5
rs2(i) = rs1(i)
Next
rs2(i) = rs1(i) will be executed 5 times, first with i=1, then with i=2 and so on until i>5

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244