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
The lookup wizard appears and the datatype automatically reverts to Text
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
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.
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
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
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:
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:
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:
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
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"));
SELECT tblMVF2.ID, tblMVF2.MVF.Value
FROM tblMVF2
WHERE (((tblMVF2.MVF.Value)="3"));
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.
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
The 3 options are shown below. No other separator characters can be used
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 £80 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 mean that instead of THREE tables, you would only require ONE (or TWO if you retain the lookup table).
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
|