Solved Many to Many VS Multi value (1 Viewer)

[Sarah.M]

Hi
If I make relationship datebase design
I face problems with MM relationship can we replace them by Multi Value much easier?
And why not?

 

[Uncle Gizmo]

can we replace them by Multi Value much easier?
And why not?

Multivalue is a relatively new addition to Microsoft Access, and many experienced developers Shun them, mainly because you can't easily upgrade them if you upgrade to MS SQL.

I blogged about them here:-

Multi Value Fields Links - Nifty Access

Multi Value Fields Links Consider a scene at a Microsoft office where an innovative individual, perhaps from the marketing team, suggests, "MS Access requires 'Multivalued Fields' 💡 - our aim is to simplify the usage of MS Access for individuals!" Envision the developers emphatically voicing...

www.niftyaccess.com

I don't believe many-to-many is harder, I would say it's much simpler. it's more like it's misunderstood.

I explained them like this in my Many to Many Blog:-

Many to Many Relationship - Nifty Access

Many to Many Relationship - 3 Video's In their simplest form Many to Many relationships are just two columns in a table. The first column representing one side of the Many to Many Relationship and the other column representing the other side of the many to many Relationship. In this particular...

www.niftyaccess.com

 

[gemma-the-husky]

Note that behind the scenes Access actually manages mvf's by modelling a many to many anyway, but saves you the effort. That's why it doesn't upsize, because that functionality doesn't exist in SQL server. Also a mvf might not do precisely what you want. What if you want to disable one of the values at some point,. but you can't delete it, because it was used some time previously. Can you do that? Can you include the deprecated value for some processes, but not for others? What if you want the mvf to ALSO contain some other data related to the items in the mvf. Can it do that?

 

[theDBguy]

I would stick with using one junction table rather than deal with two MVFs in separate tables.

 

[The_Doc_Man]

The problem with multi-value fields (MVF) is that once you try to use them, you have the issue that they will only show you a code when you wanted a full translation - or something else similar to that. Putting stuff in an MVF means you lose some control over the field, and almost invariably, you run into a situation where you needed more control.

Another issue is, suppose you have that MVF - but suddenly another table needs to use the same data as the original MVF. Now you have to duplicate that list because you can't use the list from the 1st table... its options are hidden "behind the scenes." A similar problem occurs with using the MVF in a form and then "keeping" the selected value visible.

Yes, MVFs work. No, they do not work well because of clumisness and difficulty in sharing common data across multiple tables.

 

[Pat Hartman]

I face problems with MM relationship can we replace them by Multi Value much easier?

What problem are you facing? The only advantage the Multi-Value field has is it has a slick interface that everyone likes. You can sort of simulate it with a subform if you want that look but I actually prefer having only the selected values visible. Having all the possible values visible really only works when you have a very small set of variables.

The other weakness with multi-value fields is that because their true implementation is hidden, standard SQL and VBA don't work with them.

 

[isladogs]

MVFs were introduced 14 years ago with Access 2007 along with attachment fields. Both of these were controversial from the start and to this day, most professional developers do not use them ...for VERY good reasons.

Please read my article Multivalued Fields - and why you really shouldn't use them ... before you proceed down this route.
Like the other responders above, I strongly advise AGAINST their use