Results 1 to 5 of 5
  1. #1
    5 Star Lounger Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    627
    Thanks
    168
    Thanked 77 Times in 68 Posts

    Multi-value field in Access 2013 - good idea or not?

    I'm making a small simple A2013 desktop DB to replace a spreadsheet mess. Reading in various places about multi-value fields, I've seen opinions that they should be avoided. I've seen mentions of unpredictable behavior with things like filters, or blunt statements like "I never use them!" This MS article makes me wary...
    https://support.office.com/en-us/art...ad=US&fromAR=1
    ...due to the addition of Lookups and choices when querying.

    What do the Access experts here think--use 'em or lose 'em?

    I have only one field which qualifies. Let's say it's the Color field in a Products table. There must be a minimum of 2 colors specified for each product, and a max of 4 colors. So eg:
    Coats - Blue Grey
    Shirts - White Purple Green
    Skirts - Black Red Green Yellow
    Shoes - Black Brown

    The choice is one Color multi-value field, or four fields Color1, Color2, Color3, Color4.

    The DB will be updated by me directly in the tables' datasheets. Updates are minor, less than 10 records a week, so not worth the time to dev forms.

    The main work of the DB is to output information to Excel. Once the queries are done, it should be easy to use--update the few records every week, and refresh and distribute the Excel books drawing on the info. So nothing complex going on.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I've never even considered using the multi-valued field. I converted several legacy systems to Access, and multi-valued fields always caused problems with data integrity and consistency. In your case, there is no easy way to limit the colors to 4 unless you use separate fields with a combo box. That's my take.
    Wendell

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Multi-value fields violate one of the basic rules of the relational model - one value per field. That alone would be reason enough to avoid them. The issues of data integrity and consistency that Wendell correctly pointed out, make an even stronger case to avoid them like the plague.

    On a bigger scale, multi-value fields defeat some of the many advantages and optimizations relational database systems acquired over the years. Indexes will be of no use on a multi-value field, which can have an horrible impact on performance.

    I only know reasons not to use multi-value fields and know not a single one to use them. Temporary convenience is not a reason to do something that, most likely, will cost you dearly during the life of your database.
    Rui
    -------
    R4

  4. #4
    5 Star Lounger Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    627
    Thanks
    168
    Thanked 77 Times in 68 Posts
    Thank you Wendell and Rui. You have settled the issue quite clearly.

  5. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Member Mark Liquorman sent me a message about this post, correcting some things I said in my earlier post:

    In a recent post regarding multi-value fields, you made the comment "Multi-value fields violate one of the basic rules of the relational model - one value per field.".

    The thread was closed before I could respond, but I felt it important enough to respond to you here. Unfortunately, You are incorrect in stating that multi-value fields violate the "one value per field" rule. The values contained in a multi-value field are actually contained as separate records in a hidden table, they are just presented as a single comma-delimited string for convenience.

    For example, suppose you had a table with 2 fields: Garment and Colors. You might typically have a query like this: "SELECT Garment, Colors FROM tblGarments". If Colors was a multi-value field, you might get this:
    Shirt Red,Green,White
    Pants Brown, Black

    However, if you instead wrote "SELECT Garment, Colors.Value FROM tblGarments", you'd get:
    Shirt Red
    Shirt Green
    Shirt White
    Pants Brown
    Pants Black

    This is not to say I'm a big fan of multi-value fields, I'm just trying to set the record straight. They can be very useful in certain very specific circumstances; but by-and-large I've found them to be more trouble than they are worth.
    .

    So, it turns out my comment on the one value per field issue was wrong. I thank Mark for his correction.
    Rui
    -------
    R4

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •