MVF Discussion and Uses (If you plan to Use Them) (2 Viewers)

[MajP]

Since there is a lot of discussion now on MVF, I created this thread to provide discussion on what you can do with them and how to use them. There is plenty of discussion of the cons of MVF and what you cannot do with them, so no need to rehash that here.

But assuming you are planning to use them this thread should focus on things like
1. How they work especially what is happening behind the scenes
2. How to query them, especially related data in the selection table
3. How to access in code such as Recordset of related values
4. If using them where you can get into problems.

To start with recommend reading Colin's post

Multivalued Fields

Multivalued fields (MVFs) were introduced with Access 2007. This article explains what MVFs are and how they can be used. However, most experienced developers avoid them completely. This article explains why this feature is really not a good idea

isladogs.co.uk

I will provide this example that I got from someone (not mine) that has some interesting functions that work with the MVF fields. I demo a report pulling the child records of the hidden form.


My opinion is that there is a lot you can do with MVF, but you really need to understand the limitations. My issue with them they are seen as a shortcut for novice developers, but that is likely the person who will get into trouble because they do not understand how they work. If you spend some time understanding a little how they work then you can make the decision if they are safe to use for your needs. If you do not understand them, then you could end up painting yourself in a corner where you cannot do what you want with the data.

 

[MajP]

Querying an MVF with Linked Lookup Table.
Probably the most important conversation in Colin's link is how you query the MVF when working with a lookup table. He provides an example, but it might be lost.

In my posted example above there is a lookup table for WorkType. I added an extra field "hourly Rate" to illustrate the discussion. These are BS values so if you are in the trades do not criticize.

In my companies I have a work type MVF that stores work type values. So here is the company table with the MVF.

So Work type field shows the concatenated values, but those are not really in that field. That is just a display.
If you wanted to search for "Metal Studs" this query fails with a data type mismatch

But that field is holding "somewhere" the foreign key. I know Metal Studs is foreign Key 1

Which returns

What is really going on there I cannot tell you. But somehow you are querying the hidden table.

But that would be really unusable to query that way having to know the FK. So you do your query like a normalized query but the junction table obfuscated. You want to query "Metal Studs"

Somehow this works behind the scenes. I assume behind the scenes the companyID links to the hidden junction table by company id and then joins to the worktype table by value to worktypeid.

 

[MajP]

A More Complex Query of MVF with Linked Lookup Table
To demonstrate that you can do more with the data in the lookup table. Here is a more complex query that uses the other fields in the lookup. Once you join the MVF to the lookup you can query like any other standard junction table design.

If I wanted to get the Max Rate for the work type for a given company then

So people often say you cannot do complex queries on MVFs, often this is incorrect.

 

[MajP]

Referential Integrity of MVF
To demonstrate that the MVF table enforces referential integrity on the hidden table. Try to delete a selection from your lookup table

Since I have Metal Studs as a selection (child record in hidden table) in my Company table I cannot delete it from the lookup.
Because you have no way to really access the hidden table you cannot set cascade deletes if you desired.

 

[isladogs]

@MajP
Many thanks for the links to my article. I agree that how to query MVF fields was one of the main parts of that article.
It can be done but the techniques needed are not always intuitive.

Just wondering why you wrote the second sentence in the quote below

Probably the most important conversation in Colin's link is how you query the MVF when working with a lookup table. He provides an example, but it might be lost.

Happy to answer any questions if I can help further

 

[MajP]

Just that it is a lot of information and the reader may have overlooked it, and it may be the most important area to understand. Because your value list and lookup example both return integers, I thought it might be easier to follow if the display is text. They may have missed that the "value" is a value when using a value list, but I assume it is a foreign key when you are using a Lookup table. In the article it would have been helpful to see the hidden table when using a lookup table.
Is the assumption correct that it is a value for a value list and a FK when using a lookup table? Or does the hidden table have additional fields when using a lookup? I have heard people suggest that it uses and index based query, but that does not seem to be the case just a standard query using a hidden table.

 

[isladogs]

Just that it is a lot of information and the reader may have overlooked it, and it may be the most important area to understand. Because your value list and lookup example both return integers, I thought it might be easier to follow if the display is text.

With hindsight I agree that text values would have been better. However, its worth stressing that a standard MVF field created using the wizard is always a short text field even if all values are numbers. You can see that from the sort order in the examples I provided.

Although rarely used, it is possible to have complex datatype multivalue fields using various number types (Byte/Int/Long/Sng/Dbl/Dec) and GUID.
See my article:

Complex Data Types in Access

This article explains what the various complex datatypes are and how to work with these safely.

www.isladogs.co.uk

However, these have all the disadvantages of a 'standard' MVF.

This article also gives more information about the links between the main table with MVF fields and the deep hidden tables that store the normalised data. A system table MSysComplexColumns is used to manage these.

The article also gives more examples of querying these tables.

Is the assumption correct that it is a value for a value list and a FK when using a lookup table? Or does the hidden table have additional fields when using a lookup? I have heard people suggest that it uses and index based query, but that does not seem to be the case just a standard query using a hidden table.

I haven't looked at this for a long time but I'm fairly certain the answers are the same in both cases. Its late here so I'll re-check tomorrow

However, the answer may not be straightforward. For attachment fields (a type of MVF) the number of related fields in the deep hidden table may be 3 or 6 depending on how you interrogate them. See my article:

Attached, associated, detached . . .

This article discusses the hidden attached system tables required by the use of complex datatypes in Access. It also covers an issue when deleting tables with column history memo fields as not all tables are removed by the process.

www.isladogs.co.uk

Hope that helps

 

[DakotaRidge]

My motivation for using MVFs is the visual effect in forms and reports. In forms, my MVFs use System Separator for Separator Characters. In reports, my MVFs use New Line as Separator Characters. I set Can Grow to Yes for MVFs in reports I put as many as ten MVFs in my tables/forms/reports.

Here is a form and a report that use the same data. The four textboxes with red type are MVFs. The report uses textboxes that I copied from the form. I arranged the MVFs in the report to fit a 8.5-inch wide sheet of paper.

I used no code. The data are stored in a standard table. The data are fictitious and will make no sense to a physician.

FORM

REPORT

 

[DickyP]

A fascinating thread but nowhere have I seen anything that would ever encourage me to use a MVF instead of a properly normalised database.
Obviously just my opinion.

 

[XPS35]

Here is a form and a report that use the same data

This clearly shows what is wrong with this design. You can store multiple medicines but can only store one frequency, dosage, start date and other data. So to which medicine apply those data?
The only right solution here is to have a related table with prescribed medications.

 

[gemma-the-husky]

I wanted a way to show multiple selections against a given record, which I think is similar to a MVF, although I didn't realise that, and I think I was building in A2003 which doesn't have MVFs. After I developed my solution, I realised it must be the same idea - but I think the benefit of doing it yourself is that you can manage filters and sort orders differently if necessary, which you can't do with an MVF. Also doing it from first principles makes it more tractable anyway.

 

[gemma-the-husky]

@MajP
Incidentally I think this thread title is much better. "Faking it" gave the wrong impression I think. If anything the Access MVF is faking it.

 

[MajP]

Folks, the purpose of the thread as stated was to provide some technical understanding of how MVFs work and if you do choose to use them what can you technically do with them. If you can contribute understanding such as @isladogs please contribute.

Please limit the discussion to explaining how MVFs work and how to use them.
We do not need to know that you do not use MVFs for various reasons. That dead horse has been beaten thoroughly.
We do not need to know why you like MVFs and why you use them. More appropriate somewhere else
We do not need to analyze a specific users database that uses MVFs.

It will be helpful for those going off topic to delete their threads.

 

[MajP]

Getting a Recordset of the List of Values
In thread 2 and 3 I showed you can simply join the table containing an MVF to the lookup table and do pretty much anything you can do with a regular child / Junction table. You can then use that query to get a list of the values in an MVF field. However you can also get a recordset directly from the MVF. And doing this looks like magic.

Public Sub GetList()
  Dim rs As DAO.Recordset
  Dim rs2 As DAO.Recordset2  ' The Recordset2 object contains a new property, ParentRecordset, that support multi-valued field types.
  Dim fld As DAO.Field
 
  'Get MVF values for companyID 3
  Set rs = CurrentDb.OpenRecordset("select * from tblCompanies where companyID = 3")
  'Use the value of the MVF field to open it recordset
  Set rs2 = rs!WorkTypes.Value

  'This recordset has one field called value
  Do While Not rs2.EOF
     Debug.Print rs2!Value & " of data type " & VarType(rs2!Value) & " " & (VarType(rs2!Value) = vbLong)
    
     rs2.MoveNext
  Loop
End Sub

This returns the following.

2 of data type 3 True
3 of data type 3 True
5 of data type 3 True

where 2,3,5 are the correct foreign keys.
This differs from @isladogs statement that the value are always stored as text. I am assuming that is only for a value list. The foreign keys are properly stored as long integers when using a lookup. I also assume, but untested that if the related key is text it will create a text value field.

 

[DakotaRidge]

A fascinating thread but nowhere have I seen anything that would ever encourage me to use a MVF instead of a properly normalised database.
Obviously just my opinion.

Remember, you can use both Combo Boxes and MVFs in the same database. When you conduct the needs assessment, learn how the users want to enter data and see the information in their reports. If the database has thousands of forms and reports, the multi-selections can be both types, especially if the users will be allowed to add to the database themselves. More technical users will want to do this more often.

Also, MVFs impose normalization and create queries for you.

 

[DakotaRidge]

This clearly shows what is wrong with this design. You can store multiple medicines but can only store one frequency, dosage, start date and other data. So to which medicine apply those data?
The only right solution here is to have a related table with prescribed medications.

XPS, as I stated in my post, the form and report that I showed may not work for everyone. But, it works for me. I take all of my meds and supplements in the morning at the same time. They are all for the same medical issue. I take a different med at night. It is not shown in the screenshot because it is a different med, and it is for a different condition.

If a person has a different need, then of course, his forms and reports would look and work differently. It is easy to create numerous forms/reports with MVFs, each for a different person, medicine, and disease. The user makes that decision by selecting from various models.

In many ways, MVFs do the same thing as Combo Boxes. I can a form with a hundred fields, some of which are MVFs, in a few minutes. I can then copy the textboxes from the form into a report(s) where they arrange them to fit the page. The only other thing to do is to change the separator to New Line. New Line works with the auto-expansion feature in Access reports.

The attributes in the MVFs are stored in lookup tables. Many master tables use the same information. This is where MVFs become Many-to-Many-Many. You can probably do this with Combo Boxes.

The benefit of using MVFs is that your more technical users can create new forms/reports and reconfigure existing ones. In my databases, I start with a fully loaded form that may have tens of textboxes and command buttons. I consider different uses for the information, and then I add aggregate values for costs and durations. I add images and graphics, and later I add the speech. Then I decide what each user needs in their forms and reports based on their personality and health issues. Some users don't like working with computers, so I remove as many objects as possible. Others get into the information, so they need more objects on forms and reports.

MVFs make creating several forms/reports for a particular purpose easier because they are easy to move around and reshape. It is intuitive to work with them in forms and reports.

To summarize, here are some benefits of using MVFs in Access
* Short amount of time to create
* Intuitive, so users can add and manage their information better
* Intuitive, so users can add them later
* Attractive because the backgrounds can be different colors
* The text can use different fonts
* They use different grammatical styles

And once you understand how to create a query with an MVF, that is intuitive as well. Yes, it's different, but it is easy to do.

The most important thing IMHO is to create multi-selections that appeal to your users.

 

[isladogs]

This differs from @isladogs statement that the value are always stored as text. I am assuming that is only for a value list. The foreign keys are properly stored as long integers when using a lookup. I also assume, but untested that if the related key is text it will create a text value field.

Sorry. My bad!
The comment about always being a text field does only apply to value list MVFs created by the wizard.
If you are using a lookup table, the MVF field datatype will indeed be whatever the datatype is for the lookup table field.

The two articles I linked in post #7 provide additional details not in my original article on MVFs

 

[DakotaRidge]

Sorry. My bad!
The comment about always being a text field does only apply to value list MVFs created by the wizard.
If you are using a lookup table, the MVF field datatype will indeed be whatever the datatype is for the lookup table field.

The two articles I linked in post #7 provide additional details not in my original article on MVFs

Thanks, Colin. Your post above encouraged me to try something that I had not done before. I wanted to know if MVFs would hold several URLs. My test shows that they won't do that, at least not the way that I tried it.

I would like to assign several URLs to records that store information about medicines. Over the past seven months, I have found multiple websites that have information about the interactions between different meds, their uses, foods, and recalls of medicines. I would like to return to those sites in the future when I better understand health issues that others have. Storing multiple URLs for a record would be very useful.

 

[DakotaRidge]

I wanted a way to show multiple selections against a given record, which I think is similar to a MVF, although I didn't realise that, and I think I was building in A2003 which doesn't have MVFs. After I developed my solution, I realised it must be the same idea - but I think the benefit of doing it yourself is that you can manage filters and sort orders differently if necessary, which you can't do with an MVF. Also doing it from first principles makes it more tractable anyway.

That's the good thing about Access, you can do IT your way. When A2007 was introduced, I saw MVFs as something new to learn and use. I did a presentation about them at a local UG meeting. I started using Access in 1998. It was a good program then, even better now.

It took MS a few years to explain what it had done. Other companies had MVFs before A2007. By the time MS explained how MVFs work, many professional developers had abandoned them. But they are good for them because non-coders and people who do not use combo boxes will use MVFs and get some of the same benefits. After we screw up things, developers can be hired to implement combo boxes and fix the problems. Both users and developers win. Users get a solution quickly, and then developers get hired later.

Without MVFs, users wouldn't know what to ask for. "Today, I want to select 20 companies from a list of 1000. Tomorrow I want to select another 20 companies. Some of the same companies can be in both lists. Now let me create both lists."

If MVFs can do the job, then the organization wins. Organizations should bring in the developers to do the coding, and build the analytical and statistical tools.

I just hope that AI won't take projects away from developers. It is scary thinking that all that code and those combo boxes will be at your fingertips soon. What will old people do? Colleges won't need to teach Access anymore. There won't be a need for more Access textbooks. MS will eliminate the Team. This is scarier than I originally thought.

 

[isladogs]

Thanks, Colin. Your post above encouraged me to try something that I had not done before. I wanted to know if MVFs would hold several URLs. My test shows that they won't do that, at least not the way that I tried it.

I would like to assign several URLs to records that store information about medicines. Over the past seven months, I have found multiple websites that have information about the interactions between different meds, their uses, foods, and recalls of medicines. I would like to return to those sites in the future when I better understand health issues that others have. Storing multiple URLs for a record would be very useful.

Whilst of course I wouldn't recommend it, there is no reason why you can't use URLs as MVF selections: