Results 1 to 7 of 7
  1. #1
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169

    Query of comma separated data to return related value


    I am trying to write a query that will look at a field of comma separated data (Table1; ItemCode) and return a value from another table (Table2; Order) related to the individual strings in the comma separated field. The screen shots below illustrate what I am trying to do. I am not sure where to start with this. Any help would be greatly appreciated. Thanks, Jim

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This is exactly why multi-value fields should not be used. Getting table 1 into proper structure might not be easy. Need a reliable pattern. Will there never be more than 3 values, all 3 characters each, always separated by comma and space? Any deviation from pattern (data entry error) will be an issue.

    Can be done by VBA writing to a table or series of queries. Assuming yes to the above questions, try:

    Query1
    SELECT ID, Left([ItemCode],3) As Code1, Mid([ItemCode],6,3) As Code2, Right([ItemCode],3) As Code3 FROM Table1;

    Query2
    SELECT ID, Code1 As ItemCode FROM Query1
    UNION SELECT ID, Code2 FROM Query1
    UNION SELECT ID, Code3 FROM Query1;

    There is no designer or wizard for UNION query, must type in SQL View window of query designer.

    Now join Query2 to Table2.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169
    Yes, I realize that the field of data is not ideal...unfortunately, that is what I have been given to work with. In any event, the stings are always separated by commas but can be of varying length and there may any number of values. I realized this would be cumbersome to solve. I may just need to see if I can get the data in that field in another format. Thanks for your comment.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Then would need VBA to parse the field and save records to another table. Or take the data over to Excel, parse and import back into Access.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    jhofer is offline Novice
    Windows XP Access 2000
    Join Date
    Jun 2011
    Posts
    2
    take a look at this link... it helped me parse data like you are trying to do I think

  6. #6
    jhofer is offline Novice
    Windows XP Access 2000
    Join Date
    Jun 2011
    Posts
    2
    take a look at this link... it helped me parse data .... http://support.microsoft.com/kb/210588

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How was this data enter? Was it with a multi-select listbox?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-07-2011, 10:15 AM
  2. Replies: 1
    Last Post: 05-17-2010, 12:21 PM
  3. Query doesn't return all data
    By hawzmolly in forum Queries
    Replies: 6
    Last Post: 03-26-2010, 09:12 AM
  4. Replies: 1
    Last Post: 07-31-2009, 03:57 AM
  5. Replies: 0
    Last Post: 11-12-2008, 05:18 PM

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