Appending query with multivalued fields RRS feed

  • Question

  • I have 2 tables. I want to append table 1 to table 2. The problem is that in table 1 there are 2 multivalued fields (with checkboxes for select) and ms access say that I can't append multivalued field. I red many articles and try different sql ways but nothing.. Any ideas how to do that so it works?

    Saturday, January 16, 2016 12:12 PM


All replies

  • Hi. You can do it with VBA or two queries. For example, the first query will append all the fields except the MVF, and the second query will append the MVF. The problem is to have something to identify the previously added records to make sure you're adding the MVFs to the correct record. See if this demo gives you any ideas how to do it. Hope that helps...
    Saturday, January 16, 2016 2:17 PM
  • The first query is OK, but the second... In the table with MVF I have two fields - name_of_the_field.Value and name_of_the_field. When I select the first one the error is: Query containing MVF can not contain another field.
    When I select second one the error is: Appending query can't contain MVF... 

    Monday, January 18, 2016 9:44 AM
  • Hi. Here's an example of an APPEND query to insert the values from a MVF into another table's MVF field:

    INSERT INTO tblNewTable(MVF.Value)
    SELECT MVF.Value FROM tblOldTable
    WHERE tblOldTable.ID=1

    What it means is grab all the values from the MVF in the old table for record ID #1 and insert them into the new table's MVF.

    Hope that helps... If you want a VBA solution, take a look at the demo I mentioned earlier.

    Monday, January 18, 2016 3:46 PM