Click any image to view a larger version



First Published 7 Mar 2019                     Last Updated 8 July 2022                               Difficulty level :   Easy

Section Links:
      Introduction to MVFs       Using a Value List       Using a Lookup Table       Replace & Separator Options       Upsize to SQL Server       Conclusions


1.   Introduction to MVFs                                                                                         Return To Top

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 a 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.
This table will have 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 the 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 example application 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

MVF datatype

The lookup wizard appears and the datatype automatically reverts to Text

MVF1LookupWizard

Choose ONE of the options and click Next



2.   Using a Value List                                                                                        Return To Top

Enter the values you want on the next screen. Click Next when done

MVF2LookupWizard

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.

MVF3LookupWizard

When done, click Finish. Open the table in datasheet view
A dropdown appears in the multivalue field with the values you entered previously.

MVF1TableCombo

Tick the ones you want from the list and these will appear in the same record separated by commas

MVFTable

Notice although using numbers for this example, the MVF is a text field which is sorted aplhabetically e.g. 13 is sorted before 7 etc.

The screenshot below shows the deep hidden system table   f_35E0136C98BF44A68A22B48C4CF1B2EC_MVF which illustrates how Access stores the MVF data

MVFSystemTable

NOTE:
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 ''
Access 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
FROM tblMVF1
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:

qryMVF1Like3 Instead you need to query on MVF.Value:

SELECT tblMVF1.ID, tblMVF1.MVF.Value
FROM tblMVF1
WHERE (((tblMVF1.MVF.Value)="3"));


This gives the desired result:

qryMVF1Value3

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.



3.   Using a Lookup Table                                                                                        Return To Top

First create your lookup table. For example:

MVFLookupTable

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

MVF2TableCombo

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
FROM tblMVF2
WHERE (((tblMVF2.MVF.Value)="15"));


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
FROM tblMVF2
WHERE (((tblMVF2.MVF.Value)="15"));


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
FROM tblMVF2
WHERE (((tblMVF2.MVF.Value)="3"));


qryMVF2Like3

SELECT tblMVF2.ID, tblMVF2.MVF.Value
FROM tblMVF2
WHERE (((tblMVF2.MVF.Value)="3"));


qryMVF2Value3

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

UPDATE tblMVF2
SET tblMVF2.MVF2.[Value]=25
WHERE (((tblMVF2.MVF2.Value)="15"));


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.

UPDATE tblMVF2
SET tblMVF2.MVF2.[Value]=25
WHERE (((tblMVF2.MVF2.Value)="3"));


Whereas using the ID value 5 (for 25) will update all values = 15 to 5

UPDATE tblMVF2
SET tblMVF2.MVF2.[Value]=5
WHERE (((tblMVF2.MVF2.Value)="3"));



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!



4.   Replace & Separator Options                                                                      Return To Top

Due to the nature of multivalued fields, the Replace function cannot be used to modify any of the values or the character used to separate each value

By default, multivalued fields are displayed in form & report controls with the system default separator (comma or semicolon) between each value.

FormMVFDefault
The system separator e.g. comma is NOT part of the multivalued field itself so you CANNOT use a replace function to change this separator to e.g. a new line

However, for DISPLAY purposes, this can be changed in the form/report control separator character property to use a new line between each value

FormSeparatorChar

The 3 options are shown below. No other separator characters can be used

FormMVFOptions



5.   Upsizing to SQL Server                                                                                        Return To Top

One last but very important point:

If, at any stage, you decide to upsize your datafile to SQL Server or another similar database, you need to be aware that multivalue field data cannot be migrated (along with data from attachment fields and column history in memo/long text fields)

Before upsizing, they will all need to be replaced with standard datatypes / methods of storage before you can proceed.

If you no longer need the MVF data, you should just delete it before upsizing.

However, if you need to retain the MVF data, we can recover it for you as a standard Access table.
Please note that this is a chargeable service currently charged at £60 GBP per hour.
If you only have one multivalue field to convert, it is unlikely to be more than 1 hour’s work.

Similar conditions apply as for our database conversion service but, in this case, ACCDB/ACCDE/MDB/MDE file types are all acceptable.

For further details of this recovery service, please send an email with details of your file(s).



6.   Conclusions                                                                                                          Return To Top

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.

As stated above . . .
If you ever need to upsize your database to SQL Server, multivalue fields cannot be imported and must be replaced with standard datatypes for storage.

For more details about MVFs, see this article by Microsoft: Guide to multivalued fields
The article 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 The DBGuy: MVF & Attachment Fields


UPDATE 7 July 2022
I've just discovered another detailed article on MVFs covering similar info: Using multivalued fields in queries


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!



Colin Riddington           Mendip Data Systems                 Last Updated 8 July 2022



Return to Access Articles Page




Return to Top