NauticalGent
Ignore List Poster Boy
- Local time
- Today, 04:33
- Joined
- Apr 27, 2015
- Messages
- 6,382
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:
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...
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:
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."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."
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: