A basic rule of all relational databases including Access is that each field in a table should contain only one item of data. This is important for normalisation of data.
However, with Access 2007, Microsoft introduced the idea of multivalued fields (MVFs) which appear to break the rule.
The data listed in an MVF can either be based on a lookup table or you can type in the values.
Although MVFs can seem an attractive idea for new users, most experienced developers avoid them completely.
In fact, there are many articles online about the evils of table level lookup fields. For example, see http://access.mvps.org/access/lookupfields.htm
However you create them, what you see in the MVF is masking the fact that the records are actually stored as normalised data in a deep hidden system table with a very long name that starts with 'f_' followed by a long string then ends in '_MVF' or whatever your MVF field is called.
It is this separation between what you see and what is actually stored that means MVFs can be extremely difficult to work with.
To illustrate the points in this article I have created an example database.
Click to download: MVF_Demo Approx 0.7MB (zipped)
This demo file contains two tables with multivalued fields – one using a lookup table and the other based on a value list that I entered manually.
In each case, you create an MVF by first setting the field datatype to Lookup Wizard
The lookup wizard appears and the datatype automatically reverts to Text
Colin Riddington Mendip Data Systems 13/11/2018
To provide feedback on this article, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks
Enter the values you want on the next screen. Click Next when done
When done, click Finish. Open the table in datasheet view
A dropdown appears in the multivalue field with the values you entered previously.
Tick the ones you want from the list and these will appear in the same record separated by commas
Click any image to view a larger version
Choose ONE of the options and click Next
1. Value List
Tick Allow Multiple Values on the next screen. The Limit to List option will automatically be disabled so end users will always be able to add new values
Notice although using numbers for this example, the MVF is a text field which is why 13 is sorted before 7 etc.
The screenshot below shows the deep hidden system table f_35E0136C98BF44A68A22B48C4CF1B2EC_MVF indicating how Access stores the MVF data
To show you the above screenshot, I had to resort to some trickery....
Unfortunately, you cannot see the table in the navigation pane even if system tables are made visible.
However, if you search the MSysObjects system table you will find its name. (TAKE CARE when viewing any system table)
Aha you may think - I'll just search that table using a query.
You can try to do so by copying that table name and creating a query in SQL view such as:
SELECT * FROM f_35E0136C98BF44A68A22B48C4CF1B2EC_MVF
Trying to run this query fails with a message saying Access cannot find the table ''.
It claims not to recognise the name of the table with your data!
So forget that table completely - you can't use it!
Returning to the original table, make a query based on the MVF field and try filtering for the value '3' which is in several records
SELECT tblMVF1.ID, tblMVF1.MVF
WHERE (((tblMVF1.MVF.Value) Like "*3*"));
Notice the quote marks around 3 - remember it is a text field.
This query gives the following - almost certainly not what you want:
Instead you need to query on MVF.Value:
SELECT tblMVF1.ID, tblMVF1.MVF.Value
This gives the desired result:
Now you may be happy with this result and decide that MVFs are a great idea.
I would strongly recommend you think again!
But before I explain why, lets investigate the other method of populating a multivalued field.
First create your lookup table. For example:
2. Lookup Table
Now setup your multivalued field in a separate table and this time select the first option (lookup field in a table or query).
Select the fields you want to use and click Finish when done.
When you open the table, you will once again see the MVF values in a drop down list
Select the values for each record as in the previous example
NOTE: This approach requires THREE tables. The table containing the MVF, the lookup table and another deep hidden system table.
Next we will create some queries to search records. In this case I want to search for the three records containing 15.
SELECT tblMVF2.ID, tblMVF2.MVF
In this case, no records are returned even though you know there are 3 in all
Try again searching the MVF.Value
SELECT tblMVF2.ID, tblMVF2.MVF.Value
Once again, no results are returned. The reason is that the data stored is using the ID field from the lookup table and the corresponding ID=3 (see above)
Repeating the queries using the value '3' does return the expected results
SELECT tblMVF2.ID, tblMVF2.MVF
SELECT tblMVF2.ID, tblMVF2.MVF.Value
So the query does give the correct results but only once you realise you have to filter by the bound lookup field value – normally the hidden ID field
The same rule applies if you try to run an action query on the MVF fields.
For example, you may wish to update all MVF values =15 to a new value 25
The following query doesn’t update any records
Whereas using the ID value 5 (for 25) will update all values =15 to 5
The update query only works if the bound field value is used for the filter …but you MUST enter the actual value for the update (not its lookup ID!).
This does give the required results.
Similar issues will apply with other action queries such as APPEND or DELETE
And I suggest you don’t even think about designing a CROSSTAB query involving filters or parameters!
Whilst it is possible to run action queries involving MVFs it is very messy.
It is very easy to get extremely confused about the data and make mistakes doing so.
By contrast, if you had stored your data in a normalised table with one value per record, searching, filtering & editing would be trivial.
It would also only require ONE table rather than THREE
For more details about MVFs see this article by Microsoft: Guide to multivalued fields
This explains clearly how MVFs work but glosses over most of the issues with them
Here is a link to another excellent article/demo file by an Access MVP: The DBGuy: MVF & Attachment Fields
One last but very important point :
If you ever need to upsize your database to SQL Server, multivalue fields (& attachment fields) cannot be imported.
So at that point they will all need to be replaced with standard datatypes / methods of storage
Hopefully this article will have helped explain why using MVFs is really not a good idea
Here endeth the sermon! I'll get off my hobby horse now!
Difficulty level : Easy