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

Topic

New Reply

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/article/Guide-to-multivalued-fields-7c2fd644-3771-48e4-b6dc-6de9bebbec31?CorrelationId=b40203df-eb7c-4b3e-a38d-796477d77b49&ui=en-US&rs=en-US&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.

Lugh.
~
Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

Thank you Wendell and Rui. You have settled the issue quite clearly.

Lugh.
~
Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

Reply | Quote

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.

Reply | Quote