Results 1 to 3 of 3
  1. #1
    lookingforK is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    31

    Question How to use a query to convert a multivalued field into a text field?

    Hi,



    I try to use VBA to export data from Access 2007 to Excel 2007.

    But I have a trouble with this process beause the master table contains multivalued fields via Lookup Wizard.

    So, I try to use a query to populate a "temp" table that converts a multivalued field (in the master table) into a text field, which would concatenate multiple values, and then use the ""temp"" table to export the data.

    For example, when I export data from Access 2007 to Excel 2007, the values of multivalued lookup fields based on another table become Numbers, e.g.:
    Table A (master table) has a multivalued field "Related City" whose values are based on Table B via Lookup Wizard.

    Table B has the information for all cities, i.e.:
    ID City
    1 City1
    2 City2
    3 City3
    4 City4
    5 City5
    ......

    In Access 2007, if Table A has a record with "Related City" = 'City 2, City 4', after exporting data from Access 2007 to Excel 2007, in Excel 2007, 'City 2, City 4' becomes '2; 4'.

    Now, I want to use a query to convert a multivalued field (in Table A) into a text field and populate Table C. So, I can export Table C with concatenated text values such as "Related City" = 'City 2, City 4' for the above example.

    How to use a query to convert a multivalued field into a text field for the purpose of exporting data?

    Thank you in advance.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    The multi value field only became available in 2010 (I think) and I'm not sure why they put it in, it's a terrible way to store data and encourages bad practices where normalizations of data is concerned. I do not have access to a 2010 machine but I would strongly suggest you try to normalize your data rather than try to export a multi value field.

  3. #3
    lookingforK is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    31
    Thank you rpeare.

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

Similar Threads

  1. Replies: 5
    Last Post: 06-19-2012, 10:46 AM
  2. Multivalued Look-Up Field Problem
    By Mike Wood in forum Access
    Replies: 4
    Last Post: 02-27-2012, 01:26 PM
  3. Replies: 1
    Last Post: 08-07-2011, 07:58 AM
  4. Replies: 2
    Last Post: 06-29-2011, 11:42 AM
  5. Convert number to fixed length text field
    By tpcervelo in forum Queries
    Replies: 1
    Last Post: 08-02-2010, 07:26 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