Microsoft Access Multivalued Fields: What They Are, How to Use Them, and When You Should Avoid Them

In this Microsoft Access tutorial, I will teach you about multivalued fields: what they are, how to use them, and when you should avoid them.
Craig from Brattleboro, New Hampshire (a Platinum Member) asks: I inherited a database from my predecessor. In it, she used multivalued fields to track the sales reps assigned to a customer, as there can be several. I've watched many of your videos where you say not to use them, but in all honesty, they seem to work just fine. What's the deal?
Silver Members and up get access to an Extended Cut of this video. Members will learn how to programmatically access the elements of a multivalued field using a recordset loop. I will also show you how to add selections.
MEMBERS VIDEO:
• Microsoft Access Multi...
BECOME A MEMBER:
KZbin: / @599cd
or My Site: 599cd.com/THMe...
LEARN MORE:
599cd.com/MVF
LINKS:
Evil Access List: 599cd.com/Evil
Many to Many: 599cd.com/Many...
Custom List Box: 599cd.com/Cust...
Relational Combo: 599cd.com/Rela...
SUGGESTED COURSES:
Many to Many: 599cd.com/ACX7
Multi-Select List Box: 599cd.com/ACD15
Referential Integrity: 599cd.com/ACX2
MULTIVALUED FIELD REFERENCES:
access.mvps.org...
www.isladogs.c...
tinyurl.com/yy...
www.accessmvp.c...
OTHER COURSES:
FREE Access Beginner Level 1: 599cd.com/Free1
$1 Access Level 2: 599cd.com/1Dollar
FREE TEMPLATE DOWNLOADS:
TechHelp Free Templates: 599cd.com/THFree
Blank Template: 599cd.com/Blank
Contact Management: 599cd.com/Cont...
Order Entry & Invoicing: 599cd.com/Invo...
More Access Templates: 599cd.com/Acce...
ADDITIONAL RESOURCES:
Donate to my Tip Jar: 599cd.com/TipJar
Get on my Mailing List: 599cd.com/YTML
Contact Me: 599cd.com/Contact
TechHelp: 599cd.com/Tech...
Consulting Help: 599cd.com/DevNet
Twitter: / learningaccess
en.wikipedia.o...
products.offic...
microsoft.com/...
KEYWORDS:
microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, multivalued field, mvf, lookup field, allow multiple values, lookup wizard, many to many relationship, junction table, got geddy
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[@rogerstrate5727]

Thanks Richard, I used the lookup on my little database which works OK but this video has explained a lot for me. Your videos are so helpful. "Live long and prosper"

[@599CD]

You're very welcome.

[@peterl2616]

Richard, every video I watch, I am compelled to go watch others of yours to get the "back story" on something that is really interesting that you mention in passing. You are guiding me to learn and admit what I don't know - and need to know. Well done. BTW - you had me at "Geddy". Class move.

[@599CD]

The Holy Triumvirate approves. :)

[@barrypropes89]

Good stuff, Richard. I appreciate it. I came here looking for the instructions on how to build a query with the MVF vals, but you more or less answered it with this video! LOL.

[@1000Zebedee]

I really like your tutorials Richard, and I'm learning loads 😀. Thanks for sharing your vast knowledge. I'm also very excited you are introducing other 'guests' to your customer table, namely Messers; Lee, Lifeson and Peart. Can I suggest that Maynard, Danny, Adam and Justin have a mention too as they are right up there with the best? Regards from the UK. Bill H.

[@599CD]

Love me some Tool. Good suggestion.

[@1000Zebedee]

@@599CD You know the pieces fit...😉

[@rabidfollower]

Excellent video explaining all the cons. I used a multivalue database system called Pick System, and it let you enter any data you want into a MVF. But Access's version of MVF only accepts values from existing data sources such as tables, queries, or value lists. That restriction killed it for me, and I've only used MVF once in my 20+ years. P.S. Top of your list of evil Access stuff is spaces in object names, and I'm guilty of that. And I do it with form names, query names, and report names. I've found that I don't often reference those names in complex coding situations. E.g. simple code like DoCmd.OpenForm "Order Entry Form" is what occurs to me most. Same with reports and queries -- I only invoke their names when I run or open them. But I do use proper naming for tables and fields because, as any user knows, we reference those names constantly in SQL and VBA.

[@599CD]

I wasn't even going to cover MVFs but one of my good, long-time students inherited a database with several of them and asked for my help, so I figured I do a video on it. I'm going to make a lesson for my Developer students on how to properly remove them from a database and fill a junction table properly with that data. Spaces in your form names: you bad boy. LOL. They've bitten me in the butt a few times. That plus singular vs. plural. I have a table I made years ago called "ContactsT" and every time I write code for it, I forget it's plural. So now I try to keep everything singular even if it doesn't sound right.

[@rabidfollower]

@@599CD Regarding plural or single, I look at how it sounds in plain English. We say, hey I made a customer table! We don't say "customers table" since that would be grammatically incorrect. So in your case, ContactT is correct. Regarding plurals, I tend to use them the same way Access does for reserved words: Forms, TableDefs, etc. If I have an array of movie names, I call it MovieNames.

[@vincentchikobe649]

Thanks for that great tutorial.Can that work for multiple allowances to the employee and have them added to the basic pay.

[@susanhershey7107]

I really appreciate your videos, but I wonder if you could point me towards the right one. I want to be able to choose more than one option from a list box and assign each choice to its own field in one table. Is that possible?

[@ZikryAWafi]

What's the best replacement for Attachment data type? I want to enable the user to open a PDF file within the MS Access. But with the Attachment data type, I got an SQL error when I try to integrate it with your tracking changes to data tutorial.

[@599CD]

Just store the path/filename in your table like I do in 599cd.com/Images

[@mrskilla11]

hi i watched your video on this and i tried to make a table that holds multiple values. i followed your instructions. however , i dont see the option (tick box) at the end of creating the table(with lookup) to allow multiple values. i dont know why i have tried everything. pls i will appreciate any feedback .. pls can anybody help me with this? . i dont seem to get to (9:26 ) on access .

[@599CD]

Without seeing what you've got, it's impossible for me to help you.

[@msmuzila]

needs to be new access (not 2000) = .accdb, not .mbd

[@safeershah3782]

sir just one question ............. sir i want to query all products find the one product in table how to find in ur exemple......after ur exemple finding query can,t work what i do.??? Thanks

[@599CD]

599cd.com/ask

[@mmgbtv]

Thanks, helped, but now, I have a error message that says "You cannot assign a multivalued field to the link master fields or link child fields properties." when trying to add a new sales rep.

[@599CD]

The whole point of the video was that you shouldn't use multivalued fields

[@mrskilla11]

cant find allow multiple values on the lookup field description .. can anyone help ? basically at the end of using the lookup wizard theres no option for allowing multiple values . how can i have that to show ?

[@599CD]

Old version of Access?

[@msmuzila]

needs to be new access (not 2000) = .accdb, not .mbd

[@cryoshell99]

Can you tell me how to do, after loading the form, MVF immediately marked some fields himself? I have a table of users with names and accounts (a key field) and I want to make sure that when the form is launched, the script looks at which account is being used and marks the desired name in MVF

[@599CD]

The whole point of my video is that you shouldn't use MVFs.

[@cryoshell99]

@@599CD Ok🙃

[@Tamerjacob]

I find it a solution to filter a query by excluding a customer by a sales rep Please tell me how to exclude a customer by the sales rep with other way than a multi field record

[@gregoryjeffries8475]

In a word: ICK

[@599CD]

Agreed