none
Managing multivalue fields RRS feed

  • Question

  • Understand Access has multivalue fields but its causing me some issues when implementing other features such as audit trails. Besides it is not a norm in database administration? Hence, I'm trying to do it the "right way" by creating separate tables. The following is an example of say recording the type of fruits my employees like.

    Fruits Table:
    NUM...FruitPreference
    1...Apple
    2...Pear
    3...Orange
    4...Apple Pear
    5...Apple Orange
    6...Pear Orange

    Employee Table:
    Name...NUM
    John...2
    Paul...4
    Mary...6

    I created my form and naturally I do not want the user selection to be based on NUM nor the entire list of fruit preference (imagine if there are more combinations and everything must be listed). Ideally, it should just be Apple, Pear, Orange and the respective number returned to the NUM column in my Employee Table depending on which combination they choose.

    Is this possible?

    Thanks in advance.
    Tuesday, August 7, 2018 7:13 AM

All replies

  • Hi,

    Not sure if this is what you meant but take a look at this demo at UtterAccess.

    Hope it helps...

    Tuesday, August 7, 2018 2:48 PM
  • You might like to take a look at MVFCorrector.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 the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates how modelling a many-to-many relationship type by means of a multi-valued field can be automatically replaced by a predefined conventional model in which the many-to-many relationship type is modelled by a table which resolves the relationship type into two one-to-many relationship types.

    The demo includes examples for where the multi-valued field uses values from a referenced table, or from an intrinsic value list as part of the object definition.

    Ken Sheridan, Stafford, England

    Tuesday, August 7, 2018 5:57 PM