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

[@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. :)

[@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.

[@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.

[@vincentchikobe649]

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

[@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.

[@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...😉

[@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

[@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?

[@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

[@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

[@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

[@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🙃

[@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

[@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