none
Issues with Access multivalue fields when migrating to SQL Server - is there a "roll back" in Access? RRS feed

  • Question

  • Hi

    I really liked the idea of multivalue fields in Access. But now, when migrating to SQL Server, I don't want them anymore.

    So I am looking for a solution where I "convert" "multi values" into "single value" fields. Lets say buy saving the First value in every record with multivalues.

    Is it doable?


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Saturday, October 15, 2016 11:34 AM

Answers

  • So I am looking for a solution where I "convert" "multi values" into "single value" fields. Lets say buy saving the First value in every record with multivalues.
    You don't need to lose any data by retaining only the first value of the multi-valued field per row.  What you should do is first create a table to model the many-to-many relationship type between the table and  the referenced table.  Let's assume for example that you have a table Contacts with a multi-valued field Cities which references a table Cities with a numeric primary key CityID.  You should create a table as follows:

    CityContacts
    ....CityID  (FK)
    ....ContactID  (FK)

    The primary key of the table is a composite one made up of the two foreign keys.  Then execute the following query to insert rows into the table:

    INSERT INTO CityContacts(CityID,ContactID)
    SELECT Contacts.Cities.Value, Contacts.ContactID
    FROM Contacts
    WHERE Contacts.Cities.Value IS NOT NULL;

    You can then create enforced relationships between CityContacts and Contacts, and between CityContacts and Cities.


    Ken Sheridan, Stafford, England

    • Marked as answer by ForssPeterNova Monday, October 17, 2016 12:10 PM
    Saturday, October 15, 2016 4:54 PM
  • Hi ForssPeterNova,

    According to your description, I agree with Ken's opinion. In addition you could refer to this helpful link:

    http://stackoverflow.com/questions/24894271/converting-from-access-to-sql-back-end-multi-value-fields

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Thanks for your understanding.
    • Marked as answer by ForssPeterNova Monday, October 17, 2016 12:11 PM
    Monday, October 17, 2016 3:24 AM
  • Is it possible to use the Comboboxes to add data in many to many relation ships?
    The conventional interface would be to have a subform based on the table which models the many-to-many relationship type in a parent form based on one referenced table.  The subform contains a combo box bound to the foreign key column which references the other referenced table.   You'll find an example in 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 the use of a Students parent form within in which is a subform based on a StudentCourses table which models the many-to-many relationship type between students and courses.  The demo also illustrates other types of interface, such as the use of a multi-valued list box, but note that, unlike the subform this does not allow the non-key Status attribute of the relationship type to be recorded.  It also requires code to be written to process the selections in the list box, whereas a subform is code-free and easy to implement.  To add a new course not yet represented in the Courses table the name of the course is entered into the combo box, and code in its NotInList event procedure inserts a new row into the Courses table.  For illustrations of the use of this event in other contexts see the NotInList demo in the same OneDrive folder.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, October 17, 2016 5:03 PM Typo corrected.
    • Marked as answer by ForssPeterNova Thursday, October 20, 2016 8:12 AM
    Monday, October 17, 2016 5:03 PM

All replies

  • Multivalued fields are handled by Access behind the scene in hidden tables. You can programmatically access these with the

      Recordset2 Objekt 

    The help article has examples how to run through a multivalued field programmatically. Thus you could use the Recordset2 object to read the multivalued fields and copy the contents to "normal" tables.

    I don't know if it is possible to access these hidden tables with a query.

    Matthias Kläy, Kläy Computing AG 

    Saturday, October 15, 2016 12:23 PM
  • So I am looking for a solution where I "convert" "multi values" into "single value" fields. Lets say buy saving the First value in every record with multivalues.
    You don't need to lose any data by retaining only the first value of the multi-valued field per row.  What you should do is first create a table to model the many-to-many relationship type between the table and  the referenced table.  Let's assume for example that you have a table Contacts with a multi-valued field Cities which references a table Cities with a numeric primary key CityID.  You should create a table as follows:

    CityContacts
    ....CityID  (FK)
    ....ContactID  (FK)

    The primary key of the table is a composite one made up of the two foreign keys.  Then execute the following query to insert rows into the table:

    INSERT INTO CityContacts(CityID,ContactID)
    SELECT Contacts.Cities.Value, Contacts.ContactID
    FROM Contacts
    WHERE Contacts.Cities.Value IS NOT NULL;

    You can then create enforced relationships between CityContacts and Contacts, and between CityContacts and Cities.


    Ken Sheridan, Stafford, England

    • Marked as answer by ForssPeterNova Monday, October 17, 2016 12:10 PM
    Saturday, October 15, 2016 4:54 PM
  • Hi Ken

    Thanks for this tip, I didn't know this ... but I almost never work with multivalued fields.

    Do you know how to show all columns in the query if the multivalued field has multiple columns?

    Matthias Kläy, Kläy Computing AG

    Sunday, October 16, 2016 9:35 AM
  • In a multi-valued field the values are really an implementation of a many-to-many relationship type by means of a set of foreign key values, so in my example the Cities column in Contacts would have a set of numeric values, each of which references a row in the Cities table.  To show multiple columns from the referenced Cities table in a query's result table, therefore, you'd join the two tables as below and return whatever columns you wish from the referenced table, e.g.

    SELECT Contacts.FirstName, Contacts.Lastname,
    Cities.City, Cities.County
    FROM Contacts INNER JOIN Cities
    ON Contacts.Cities.Value = Cities.CityID;


    Once the relationship type has been modelled in the conventional way by means of a CityContacts table the same result table would be returned by joining the three tables:

    SELECT Contacts.FirstName, Contacts.Lastname,
    Cities.City, Cities.County
    FROM (Contacts INNER JOIN CityContacts
    ON Contacts.ContactID = CityContacts.ContactID)
    INNER JOIN Cities ON CityContacts.CityID = Cities.CityID;

    One important point worth making is that when you model a many-to-many relationship type in the conventional way by means of a table which resolves it into two or more one-to-many relationship types (sometimes colloquially called a 'junction' table), the relationship type can have non-key attributes.  In the CityContacts table for instance you could have a column of date/time data type representing the date from which the contact was related to each city.  When the relationship type is modelled by a multi-valued field on the other hand, this is not possible.  For this reason, along with the inhibition on portability of the data, I, like you, never used a multi-valued field.

    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Sunday, October 16, 2016 12:07 PM Typo corrected.
    Sunday, October 16, 2016 12:05 PM
  • For this reason, along with the inhibition on portability of the data, I, like you, never used a multi-valued field.

    Hi,

    As Matthias and Ken, I do not use multi-valued fields. I have automated all relations in a database, and in that systematics there is no place for multi-valued fields.

    Imb.

    Sunday, October 16, 2016 3:44 PM
  • Hi ForssPeterNova,

    According to your description, I agree with Ken's opinion. In addition you could refer to this helpful link:

    http://stackoverflow.com/questions/24894271/converting-from-access-to-sql-back-end-multi-value-fields

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Thanks for your understanding.
    • Marked as answer by ForssPeterNova Monday, October 17, 2016 12:11 PM
    Monday, October 17, 2016 3:24 AM
  • Thanks all for advicing me. I am really grateful.

    Matthias and Ken helped me to understand what a multivalue field is like.
    Imb, you have given many inputs during my way thru Access.

    This time I found great help from the link David provided so I mark David and Ken as answers.

    I am really grateful.


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Monday, October 17, 2016 12:12 PM
  • One more question ...

    Is it possible to use the Comboboxes to add data in many to many relation ships?


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Monday, October 17, 2016 1:41 PM
  • Hi Peter,

    Pardon me for jumping in, but unless I am not understanding your question properly, it's exactly how you would add records to a many-to-many table (junction table). For example, let's say you have the following table structure:

    tblProducts
    ProductID
    ProductName
    etc...

    tblVendors
    VendorID
    VendorName
    etc...

    tblVendorProducts
    VendorProductID
    VendorID
    ProductID
    ProductPrice
    etc...

    You would use a Combobox to select VendorID and another Combobox to select the ProductID to add records to tblVendorProducts.

    Hope it helps...

    Monday, October 17, 2016 2:49 PM
  • Is it possible to use the Comboboxes to add data in many to many relation ships?
    The conventional interface would be to have a subform based on the table which models the many-to-many relationship type in a parent form based on one referenced table.  The subform contains a combo box bound to the foreign key column which references the other referenced table.   You'll find an example in 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 the use of a Students parent form within in which is a subform based on a StudentCourses table which models the many-to-many relationship type between students and courses.  The demo also illustrates other types of interface, such as the use of a multi-valued list box, but note that, unlike the subform this does not allow the non-key Status attribute of the relationship type to be recorded.  It also requires code to be written to process the selections in the list box, whereas a subform is code-free and easy to implement.  To add a new course not yet represented in the Courses table the name of the course is entered into the combo box, and code in its NotInList event procedure inserts a new row into the Courses table.  For illustrations of the use of this event in other contexts see the NotInList demo in the same OneDrive folder.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, October 17, 2016 5:03 PM Typo corrected.
    • Marked as answer by ForssPeterNova Thursday, October 20, 2016 8:12 AM
    Monday, October 17, 2016 5:03 PM
  • Hi Ken

    Thanks for the OneDrive link. I really like the options you give in "StudentCourses"


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Thursday, October 20, 2016 8:13 AM