Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Multivalued Fields . . . and why you really shouldn't use them!

Return to Home Page

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        Updated 07/03/2019

Return to Top
* Required

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

MVF datatype

Click any image to view a larger version

MVF1LookupWizard

Choose ONE of the options and click Next

1. Value List

MVF2LookupWizard MVF3LookupWizard

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

MVF1TableCombo MVFTable

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

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 ''.

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

 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:

qryMVF1_Like3 qryMVF1Value=3

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.

First create your lookup table. For example:

2. Lookup Table

MVF 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

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

qryMVF2Value=3 qryMVF2_Like3

 

 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"));

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.

 

 UPDATE tblMVF2

 SET tblMVF2.MVF2.[Value]=25

 WHERE (((tblMVF2.MVF2.Value)="3"));

 

 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!

Return to Access Articles

Difficulty level :   Easy

3. Upsizing to SQL Server

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 will need to be replaced with standard datatypes / methods of storage

 

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

 

 

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!

One last but very important point:

 

If, at any stage, you decide to upsize your datafile to SQL Server, 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)

So at that point they will all need to be replaced with standard datatypes / methods of storage before proceeding

 

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

However, if you wish to retain the MVF data, we can recover it for you as a standard Access table.

Please note that this is a chargeable service costing at £50 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 the database conversion feature but, in this case, ACCDB/ACCDE/MDB/MDE file types are all acceptable .

 

For further details of this recovery service, please email info@mendipdatasystems.co.uk with details of your file(s)

4. Conclusions