I am importing the Zip code spreadsheet downloaded from the US Postal Service. There is a field of acceptable alternate cities that has each value separated by a comma and a space (see Existing Structure table below). I would like to create a table of these to be "children" of the zip code so that if the primary city for the zip code is not the one to be used for a certain address the user can choose one of the acceptable alternates (see Desired "Child" Records table below).
Does anyone know of a technique or code, either in Excel or Access, or can suggest an approach that could create such child records in a separate table that could then be linked to the parent via the zip code? I can do it in Excel via Text to Columns (which is relatively quick to do so would be an acceptable manual pre-step) and Transpose formulas, but there are 10,000 or so records making the transposing extremely time consuming. It would also be good to have something repeatable in order to update periodically.
I had no trouble importing into Access and even re-defining the acceptable_cities field as a multivalue field. However, it seems to recognize all the text and commas as 1 value, effectively no different than a text field.
Thanks
Existing Structure
Zip primary_city acceptable_cities 41465 Salyersville Bethanna, Burning Fork, Carver, Cisco, Conley, Cutuno, Cyrus, Duco, Edna, Elsie, Ever, Flat Fork, Foraker, Fredville, Fritz, Gapville, Gifford, Hager, Harper, Hendricks, Ivyton, Lickburg, Logville, Maggard, Marshallville, Mashfork, Seitz, Stella, Sublett, Swampton, Wonnie
Desired "Child" Records
Zip Alternate City 41465 Bethanna 41465 Burning Fork 41465 Carver 41465 Cisco 41465 Conley 41465 Cutuno 41465 Cyrus 41465 Duco 41465 Edna 41465 Elsie 41465 Ever 41465 Flat Fork 41465 Foraker 41465 Fredville 41465 Fritz 41465 Gapville 41465 Gifford 41465 Hager 41465 Harper 41465 Hendricks 41465 Ivyton 41465 Lickburg 41465 Logville 41465 Maggard 41465 Marshallville 41465 Mashfork 41465 Seitz 41465 Stella 41465 Sublett 41465 Swampton 41465 Wonnie