Multi-Valued fields (1 Viewer)

NauticalGent

Ignore List Poster Boy
Local time
Today, 18:37
Joined
Apr 27, 2015
Messages
6,286
Good morning AWF,

I have been aware of the existence of Multi-Valued fields (MVF) for some time but never took the time to learn about them. They seemed like a gimmick for every-day users and a step away from normalization so I left it alone. However yesterday, I found some free time and was looking through the MS Access 2010 Programmer's Reference book (ISBN 978-0-470-59166-6) and came across this bit:

"When you create a lookup field in Access 2007 or Access 2010, you can optionally choose to allow that field to store multiple values. For example, say you have a table of students, and you want to track their favorite colors. Traditionally, you accomplish this by using three tables: one for Students, one for Colors, and a table in between these two called a junction table. Multi-value lookup fields, also known as complex fields, can also be used to store the favorite colors for a particular student as a single field in the Students table. A multi-value lookup field can store many related records in a single field value. You can think of them as an embedded or nested Recordset in a field for a particular record. In fact, that’s exactly how you work with multi-value lookup fields in DAO. You might look at that list and think to yourself, “Isn’t that denormalized?” Well, not to worry — the values for multiple-value fields are stored behind the scenes in related tables.

Since these values are stored behind the scenes, they are not available for viewing or querying directly. However, Access does all of the work to maintain these relationships and lets you, as the developer, focus on data manipulation in a natural manner — by using DAO or SQL. Multi-value lookup fields can be useful for simple one-to-many relationships, but they have one major limitation. The nested Recordset for a multi-value lookup field contains only one column called Value. This is true whether the lookup field is created using the Access interface or DAO. To extend the example a little, it might be nice to know the semester in which a student attended a particular class, and even the grade he received for the class. That is not possible using a multi-value lookup field because those fields store only one field per record."
The chapter on "Why Use DAO?" even went on to provide sample code on how to work with MVF's and so on and so forth. In the past I simply made a separate table for multiple choices - it just seemed easier to work with, cleaner and more importantly, normalized.

My question to the forum is this: Has anyone used this feature and if so, what benefits (if any) have you experienced? Additionally, if it was a less-than-favorable experience, I would be interested on hear that too...
 
Last edited:

Minty

AWF VIP
Local time
Today, 22:37
Joined
Jul 26, 2013
Messages
10,355
The main problem (that I see) is that neither the Lookup Field or Multivalued Field will scale out to another backend (SQL Server etc). So all the cleverness that MS claim is being handled silently goes out of the window.

The other problem I've seen on here more than once, is that the lookup fields, whilst initially looking like they work , do "hide" the true values being manipulated and cause issues with what is being searched / looked up in code.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:37
Joined
Feb 19, 2013
Messages
16,553
multivalue fields use much the same concept as attachment fields - a recordset within a recordset. So you lose sight if one of the key elements in a query design (the join table).

From a presentational perspective, they look good but have limitations for more sophisticated users and are often misused by new adoptors of access - particularly those migrating from Excel where presentation takes priority over efficient design. Anyone with a bit of VBA knowledge can create their own multivalue user experience with additional features such as conditional formatting.

The example used in your quote is a fairly safe use for multivalue fields - a limited and fixed range of options - days of the week, months of the year are others, but many will use it for example to select employees from an employee table - which is dynamic as employees join, leave, take periods of absence, get promoted etc.

The reason Access is a rapid development tool is because it comes with many objects and controls each with many events and properties (in my view the most useful of these being the continuous form). Go to another development environment such as Visual Studio and many of these controls exist but at a much more basic level 'off the shelf', you have to add functionality in to do what access already does. (And no, continuous forms do not exist, VS developers struggle to even understand the concept) - but ultimately you can add much more functionality.

In my opinion, multivalue fields have a place but mixes presentation and table design and dumbs down what can be achieved. If as a developer you want to move to bigger and greater things, you need to keep in touch with the basic components - and not mix presentation with data
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:37
Joined
Jul 9, 2003
Messages
16,245
I have avoided using them so far, but I kept a link to this excellent video on manipulating multi value Fields with VBA just in case I decided to try them out one day...

Access2010 MultiValued ComboBox
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 18:37
Joined
Apr 27, 2015
Messages
6,286
Thanks guys, I agree with your observations it just kind of confused me to see favorable things said about them in an otherwise very informative programmers book.

There was even a paragraph or two about few instances when calculated fields could be a good thing...should have known at that point someone was singing the Micro$oft company song!

I haven't watched the video yet Tony, but I will make a point of doing so. Nifty (pun intended) new stuff on your YouTube channel too...
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 18:37
Joined
Apr 27, 2015
Messages
6,286
I have avoided using them so far, but I kept a link to this excellent video on manipulating multi value Fields with VBA just in case I decided to try them out one day...

Access2010 MultiValued ComboBox

Handy video Tony, I also learned two neat little debugging tips too. Thanks for sharing, the silent movie effect was a small hurdle but not too bad!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:37
Joined
Feb 19, 2002
Messages
42,976
As a presentation object, MV fields look good. as a programming object, they are more trouble than they are worth. They handle one and only one field as the article mentioned plus they require learning new SQL Syntax to actually use them in queries and new VBA syntax to use them in code.

Don't waste your time for such little gain (pretty controls). You can get a similar effect by creating a subform. You just have to play with the display to hide the mechanicals and make the subform look like a control.
 

Users who are viewing this thread

Top Bottom