Results 1 to 3 of 3
  1. #1
    dgj32784 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    22

    Convert Comma Separated List String to Records

    I have a field that lists the county names that a company does business in. The field type is memo and the string ranges from no entries to 377 characters. The users have entered counties into this memo field in the following format: CountyName1, CountyName2, CountyName3, etc. Each business has a unique ID and there are 509 businesses.



    My question is...How can I quickly convert this field to a record-based table, where there will be multiple records of a given business but only a single record for a business and county combination? The primary key would then shift from the unique ID to a shared key set on the unique ID and county.

    This is a common problem I've dealt with when cleaning up behind Excel users who migrate to Access. In the past I have done this in a manual way, but given the number of business records and the number of counties that may be listed for a given business, I'd rather automat this in some fashion.

    Any advice would be greatly appreciated.

    Thanks,
    David

  2. #2
    kennejd is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    188
    You could write some vba code to loop through each field....or possibly a union query.

  3. #3
    dgj32784 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    22

    Problem Resolved

    Well, I didn't go with either of your suggestions but I think your reply helped me to think outside the box a little. Since my counties were listed with a ", " between each name, I ran a query with the replace function to swap out the comma and the space with a unique character "|", the unique ID, and a comma. I then added the unique ID to the beginning of this new string separating it with a comma using concatenation. My new string was then: UniqueID1,CountyName1|UniqueID1,CountyName2, etc. I then copied this column of data over to a text editor and replaced all the "|" with a paragraph mark and now I have a comma separated record-based accounting of businesses and the counties they work in.

    Thanks for taking the time to throw some ideas out there.

    David

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

Similar Threads

  1. Replies: 1
    Last Post: 03-14-2011, 04:27 PM
  2. Replies: 3
    Last Post: 03-25-2010, 12:31 PM
  3. Replies: 1
    Last Post: 07-31-2009, 03:57 AM
  4. Convert string to date
    By ~SwAmPdOnKeY~ in forum Queries
    Replies: 4
    Last Post: 09-11-2008, 07:19 PM
  5. Creating a string from all records in a table
    By ocordero in forum Programming
    Replies: 2
    Last Post: 08-07-2006, 09:21 AM

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