Results 1 to 13 of 13
  1. #1
    rjgriffin46 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Location
    New Jersey
    Posts
    128

    Converting Multivalue Fields to Records

    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

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    ZipDistribute09.zip This will do the job.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need a bit of vba code to split the acceptable_cities field and save the results. This will run through your entire set of records - don't know if the fact you have made acceptable_cities a multivalue field will cause a problem, but if so, remove the multivalue aspect - they shouldn't be used for this sort of thing anyway


    Code:
    dim I as integer
    dim strArray() as string
    dim rst as dao.recordset
    
    
    set rst=currentdb.openrecordset("SELECT * FROM myTable") ' substitute for your table name
    while not rst.eof 
        strArray=split(rst!acceptable_cities,",")
        for I=0 to ubound(strArray)-1
            currentdb.execute("INSERT INTO ACTable (Zip,AlternateCity) VALUES ('" & rst!Zip & "', '" & rst!acceptable_cities & "')")
        next I
        rst.movenext
    wend
    rst.close
    set rst=nothing
    you could put this in the click event of a button on a form or in a module to be called from anywhere

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I suspect Ajax meant to use the array with the loop counter.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I did - it was late

    currentdb.execute("INSERT INTO ACTable (Zip,AlternateCity) VALUES ('" & rst!Zip & "', '" & strArray(I) & "')")

  6. #6
    rjgriffin46 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Location
    New Jersey
    Posts
    128
    Thanks for these awesome responses. Of course, a couple of questions:

    davegri : I downloaded and ran/executed the button on the form which worked exactly as described. It seems to do 1 zip code at a time. How would I make that run through the entire PO_ZipList table?

    Ajax : The line of code you included in your response to pbaldy was already in your initial response. Is that correct?

    Thanks again for your timely and fruitful responses.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Ajax : The line of code you included in your response to pbaldy was already in your initial response. Is that correct?
    yes - the line was but the second post was correcting it

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    From davegri: It does expand the entire po_zipList into tblZipList. Use the navigation buttons at the bottom of the form.

  9. #9
    rjgriffin46 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Location
    New Jersey
    Posts
    128
    davegri : My apologies. I reset tblZipList and ran the Expand button procedure again and saw that it did process both PO_ZipList records. I will try this on the full PO_ZipList table which has 42,521 records of which 9,010 have at least 1 entry in the acceptable_cities field. Will let you know if I encounter any problems.

    Thanks so much!

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I didn't take into account that there might not be any acceptable_cities for a given zipcode, so the code will throw an error for a null field. Use the below code to replace corresponding sub:
    Code:
    Public Sub cmdBegin()    
        Dim rstZip As Long
        Dim rstCities As String
        Dim rst As DAO.Recordset
        Dim db As DAO.Database
        Set db = CurrentDb
        Set rst = db.OpenRecordset("PO_ZipList")
        With rst
            .MoveFirst
            While Not rst.EOF
            If Len(rst!PO_acceptable_cities & vbNullString) > 0 Then
                rstZip = rst!PO_Zip
                rstCities = rst!PO_acceptable_cities
                Call subBreakout(rstZip, rstCities)
            End If
            .MoveNext
            Wend
        End With
    End Sub

  11. #11
    rjgriffin46 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Location
    New Jersey
    Posts
    128

    Talking Testing Results

    I tested davegri 's database since that seemed the easiest to do. I was able to make it function with some changes and additions. The following is in a label I added to the form:

    ------------------------------------------------------------------
    Notes: This version works with the following exceptions:


    1. It aborts on the record with O' Neill as an alternate city (Run-time error '3075': Syntax error (missing operator) in query expression "O' Neill')'. When delete the single quote (') runs to end and produces 14,201 records.


    2. It is dropping leading zeros on Zips less than 5 numbers excluding 0, seemingly treating the zip code as a number vs. text.
    2.1 Changed all references in VBA from As Integer to As String but still creating without leading zeros. So, created query to pad zeros.


    3. For zip 41465, 1 record has a blank name and the other is missing because the Zip Codes table doesn't have it. Will have to investigate the import to see if got truncated or otherwise why.
    3.1 Re-imported and found it did truncate 41465. After internet search, while in Import wizard, changed data type from Text to Memo to preclude Access assigning it 255 characters. Then the full field imported resulting in 14,203 records because one other zip (40207) also exceeded 255 at 282.


    4. It aborted on the 1st record where acceptable cities was Null. So created "Zip Alt City Only Query" to include only those with acceptable cities and used that as record source. However, I'll see if I can incorporate the updated code last posted by davegri.


    ------------------------------------------------------------------

    In addition I changed the file and field names to suit our naming conventions (my apologies for poor adherence to best practices).
    I've attached my copy although I had to delete about 3,000 Zip Code records to get it under the attachment size limit.

    Though I haven't yet created a from to use the ZipAltCitiesFilled query as a lookup record source, I suspect it will work OK. However, any further comments and suggestions would be welcome.

    Many thanks to both davegri and Ajax!
    Attached Files Attached Files

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It aborts on the record with O' Neill as an alternate
    you need to use the replace function to replace a single quote with two single quotes
    It is dropping leading zeros on Zips less than 5 numbers excluding 0, seemingly treating the zip code as a number vs. text
    I've not looked at davegri's code but probably it is treating as numbers there so losing the preceding zero

  13. #13
    rjgriffin46 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Location
    New Jersey
    Posts
    128
    I was able to utilize the code offered and it has served its purpose. I'm extremely grateful and will close this thread.

    Thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 11-14-2015, 07:17 AM
  2. Convert multivalue fields back to normalized tables
    By TXStateMom in forum Programming
    Replies: 5
    Last Post: 08-26-2014, 03:21 PM
  3. Move to SharePoint and multivalue fields?
    By Mercator in forum SharePoint
    Replies: 2
    Last Post: 12-27-2012, 07:06 PM
  4. How to display multivalue fields
    By Trojnfn in forum Access
    Replies: 5
    Last Post: 10-22-2012, 03:48 PM
  5. Converting dates from a field with blank records
    By NOTLguy in forum Programming
    Replies: 3
    Last Post: 10-14-2010, 06:38 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums