none
Make table with multi valued fields in access query RRS feed

  • Question

  • Hi, I am using Microsoft Access.
    I have a multi select list in my table and I have make a query from it. Now I have made some change to my query and wants to make a new table from my query.
    When I choose the make table mode in query design and click on run button it says that "multi-valued fields are not allowed in select into statements" And I have search for it and it seems that its because of my multi select list in my query. I want to have it in my query!
    Is there any way to make table from my query with my multi select list text value in it?
    Wednesday, November 30, 2016 6:54 AM

All replies

  • Instead of the "Select Into" (Make Table Query): Create the results table beforhand with the proper field definitions and use an Append Query (Insert Into). If you run this repeatedly, first delete all rows from the results table before you run the Append Query.

    Matthias Kläy, Kläy Computing AG

    Wednesday, November 30, 2016 9:38 AM
  • I have tried "Append Query" but it says that "An INSERT INTO query cannot contain a multi-valued field."

    Is there any way?

    Wednesday, November 30, 2016 9:42 AM
  • Can you post the SQL of the query?

    Matthias Kläy, Kläy Computing AG

    Wednesday, November 30, 2016 9:56 AM
  • Here is Append Query SQL:

    INSERT INTO chchchch ( ID, tarikh, Name1, Fname, Pname, Shsh, DBirth, MBirth, MTahsilat, Brand, Tolidkonande, Varedkonande, Vasete, Grade, foroshgaha, mname, mphone, building, tabaghe, metraj, address, phsabet, mobile, website, email, tozihat, Sabk, tari )
    SELECT Data.ID, Data.tarikh, Data.Name1, Data.Fname, Data.Pname, Data.Shsh, Data.DBirth, Data.MBirth, Data.MTahsilat, Data.Brand, Data.Tolidkonande, Data.Varedkonande, Data.Vasete, Data.Grade, Data.foroshgaha, Data.mname, Data.mphone, Data.building, Data.tabaghe, Data.metraj, Data.address, Data.phsabet, Data.mobile, Data.website, Data.email, Data.tozihat, Data.Sabk, Mid([Data]![tarikh],5,2)+Mid([Data]![tarikh],3,2)+Mid([Data]![tarikh],1,2) AS tari
    FROM Data
    WHERE (((Data.ID)=[Forms]![list]![ID]));

    chchchch is the table that the query is going to append to And Sabk is the multi-valued field.


    • Edited by Seena Fallah Wednesday, November 30, 2016 10:00 AM
    Wednesday, November 30, 2016 10:00 AM
  • Multi-valued fields (MVF) are one of many "features" that should never be used!! (and you are seeing one of many headaches they create) and they go against proper database normalization principles too since everything is hidden and we can't directly access the tables!  They are quick and dirty solutions to what could already easily be accomplished through normal table setup, so little gain but the potential for major headaches.  It should also be noted, that if ever you wish to upgrade your BE to another RDBS MVF will pose a real problem.

    Take a look at A.D. Tejpal's sample database, found at http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45, you should be able to learn a few tricks.

    https://support.office.com/en-us/article/Using-multivalued-fields-in-queries-6f64f92d-659f-411c-9503-b6624e1e323a

    Should you go the route of VBA, see: https://msdn.microsoft.com/en-us/library/office/ff821054.aspx for some basics on manipulating MVFs.


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net



    Wednesday, November 30, 2016 2:45 PM
  • Oh! :D

    So I want to have an option that User can choose multi options from my list!

    Do you have any solution for it?

    Wednesday, November 30, 2016 2:50 PM
  • You have to create a related table to store the choices.

    Say you want to track people and phone numbers (as an example), you'd create a structure similar to

    T_Contacts
    ******************
    ContactID
    FirstName
    LastName
    ...

    T_Contacts_PhoneNo
    ******************
    PhoneId
    ContactId
    PhoneNo
    PhoneType
    ...

    As such you could enter as many telephone numbers relating to each contact as you wish.  The same would be true for allowing a user to make multiple selections.  You use a similar setup, and then you insert a subform to allow the entry(ies) or a listbox allowing multiselect (but this then required some VBA coding to push/pull their selection(s).


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Wednesday, November 30, 2016 2:55 PM
  • For example I have a table that it include my list for User to choose and I have a form that it has a combo box that shows the list

    I want User to choose as many as he/she like from the list in combo box. Please give a solution for it.

    Thanks.


    • Edited by Seena Fallah Wednesday, November 30, 2016 3:00 PM
    Wednesday, November 30, 2016 2:59 PM
  • Switch the combo box form to continuous and insert it into your main form.

    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Wednesday, November 30, 2016 3:10 PM
  • What do you mean about continuous?

    Wednesday, November 30, 2016 3:32 PM

  • Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Wednesday, November 30, 2016 3:48 PM
  • ok when I choose continues form what should I do?

    I want my User to select multiple from the combo list without select multiple option in access!

    Wednesday, November 30, 2016 4:08 PM
  • A multi-valued field is no more than a crude way of modelling a binary many-to-many relationship type.  The conventional way is to model the relationship type by a separate table which resolves it in two one-to-many relationship types.  You'll find an example as StudentCourses.zip in my public databases folder at:

     

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

     

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly. 

     

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

     

    This little demo file illustrates a number of interfaces for a simple many-to-many relationship type like this.  The conventional interface is by means of a parent form in single form view, within which is a subform in continuous forms view.  It is possible to use an unbound multi-select list box (not a combo box) and this is illustrated in the demo, but it requires code to be written to read and write the values from and to the table which models the relationship type.  A conventional form/subform is code-free, and also allows the relationship type to have additional non-key attributes such as the Status attribute in my demo.

     

    However you do it, you won't be able to write the data to another table with a 'make table' query.  You would need to firstly write the rows from the referenced table to one table, and then the matching rows from the referencing table to another table.  The real issue here is why you are using a 'make table' query at all.  They rarely have any useful role in a well designed database.


    Ken Sheridan, Stafford, England

    Wednesday, November 30, 2016 6:47 PM
    • Edited by Seena Fallah Wednesday, November 30, 2016 7:05 PM
    Wednesday, November 30, 2016 7:05 PM
  • Hi

    For that thread, it is impossible to merge the query to word with variable in it. 

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 15, 2016 2:59 AM
    Moderator