Do Multivalue fields in Access, break normalization rules.

[hvsummer]

[z[Do Multivalue fields in Access, break normalization rules.]]


Hi everyone,
This is my first topic, clap clap clap
when a lot of people think that Multivalue Field will break database normalization rule, that's not correct.

first thing, to understand what is MVFs of Microsoft Access,
we have to imagine it, to see it.

when you set up new multivalue field, you can choose many value into 1 field as you want.
But it does not actually save in table like "A, B, C" for example.

I have got what Microsoft did about 10 mins ago

they make that table have hidden item, I mean, when you choose multi value in a field, all value save in the fieldname.value that we can't directly access to it on that table.

this function somehow similar to Unique record in Query.
let's imagine, You have to create 2 record with ID A and B, but the rest field are the same.
with MVFs, your table still have 2 record with ID A and B
but they all hidden, and only show 1 record "A, B" as a row.

another example,
you have table like this

ID Name No
1 AB 10
2 AB 10

if you use MVFs, it should be like this on screen

ID Name No
1,2 AB 10

but actually, data saved like this

---------ID Name No
value1 1 AB 10
value2 2 AB 10

the same as first normal table. And you can query it without any difficult. Even use it to join between table in query. just add .value in name of MVFs.

[zmbd]

You are correct in that multivalued fields in and of themselves do not break the normalization concept in their behind the scenes implementation.


They do break the intent of normalization, along the same lines that that lookup fields at the table level do (read here The Evils of Lookup Fields in Tables) in that they hide the actual relational process from the user and anyone else that follows allowing for (IMHO) very sloppy and hard to maintain database design - and by inference 1NF rules.

* First Normal Form (1NF). The first form requires that there be no multivalued items or repeating groups. All fields must be atomic, which means that the data is the smallest unit possible and can't be further divided.

That the behind the backstage implementation forces the 1NF doesn't change the fact that the database is not properly normalized at design.

MVF needlessly complicate the VBA/Macro coding
MVF needlessly complicate SQL/Query structure
MVF prevent validation of the junction tables as they are now hidden system tables.
MVF , afaik, are not available in any other RDBMS; thus, upgrading to something like MySQL, MariaDB, SQLServer, Oracle, etc... isn't likely to happen.


IMHO there is one and only one reason to ever use MVF or LUF at the table level and that is for share-point/access applications.

[hvsummer]

I like this MVF because

first, it let me tick multivalue from dropdown list (combobox) and store as a group of operation ( I usually have a lot of item inside an operation).

second, I can manager those operation in big picture, I don't have to see each small piece of them to check.

Third, I don't have anytrouble using MVF coz I use 1 simple query and call all field from table containing MVF (like "SELECT a AS ABC, b.value AS CDE FROM table1") and treat it like a normal table without MVF.

fouth, same as first, I can choose multi value from droplist in combobox and avoid using Listbox since listbox multivalue need addition VBA code to process, and listbox area's info and size are not as flexible as droplist of combobox

[zmbd]

I like this MVF because

hvsummer, I'm not saying that there's not something to like about the MVF; however, that is not the title of your article.
MVF (aka table level lookup-fields) violate the very basic principle of normalization for the reasons cited in my prior post (ibid link).

+ As your database grows, your going to find that this "feature" can become very difficult to manage, and there are performance hits.

+ You cannot enforce data integrity using lookup fields with the "allow multiple values" option selected (try it in the lookup wizard, it wont let you) this risks orphaned records in the data set.

+ The only "good" reason to ever use multivalue fields is if you're using SharePoint Services as this is the only means available to establish table relationships in the web-interface

+ Keep in mind that upgrading to a mature RDMS such as SQL-Server, MySQL, MariaDB, Oracle may very well convert a multivalue field to a Memo field, so keep upgrading in mind when making your choice

To address your other points:

first, it let me tick multivalue from dropdown list (combobox) and store as a group of operation ( I usually have a lot of item inside an operation).

fouth, same as first, I can choose multi value from droplist in combobox and avoid using Listbox since listbox multivalue need addition VBA code to process, and listbox area's info and size are not as flexible as droplist of combobox

:-D
You have really one point here: You do not want to have to use either Access-Macro or VBA code for the operations you are attempting. Or, you do not want to create the join table between the two related tables for a M:M relationship.

Because we don't know the nature of your applications, it's impossible to help you find the solutions that might avoid the issues you're experiencing, not something that is within the scope of an "insights" article.

By the way, VBA coding is not that difficult and quite often there are SQL methods that will handle the application needs.

second, I can manager those operation in big picture, I don't have to see each small piece of them to check.

This sounds an awful lot like the prior point(s).
If you are talking about viewing related records between related tables, then the Form-Subform construct is your friend and is quite often just as flexible, if not more so, than any list/combo-box. Once again, there may be a need for some VBA (such as here #17 ) depending on what you are attempting to do; however, I find that often just to view records no vba is required.

In a new thread, if you can explain a little more about what you are attempting, then maybe we can provide you with better alternatives than the lookup field.

Third, I don't have anytrouble using MVF coz I use 1 simple query and call all field from table containing MVF (like "SELECT a AS ABC, b.value AS CDE FROM table1") and treat it like a normal table without MVF.

Let's take the article here from Microsoft
Specifically two points:
Understanding the bound value and the display value in a lookup field

It's important to understand the distinction between a lookup field's display value and the bound value. The display value is automatically shown in datasheet view by default. However, the bound value is what is stored, what you use in query criteria, and what Access uses by default in joins with other tables.

You do not see the actual value "stored" in the table with the MVF, you see the "user friendly" data. The table that has the primary-key and the associated data is hidden... this will make writing complex queries much more difficult for anyone that doesn't understand MVF (read on)

Searching for more than one value in a multivalued field

Sometimes, you need to search for a match on more than one value. Suppose you want to see those issues in which both "Kelly Rollin" and "Lisa Miller" are among the values in the AssignedTo field. The bound value for Kelly Rollin is 6, and the bound value for Lisa Miller is 10. To specify several criteria for a multivalued field, you can use the AND and the OR operators.

Now "Kelly Rollin" and "Lisa Miller" are shown in the table you want to search on as this:
[Issues_Title][Issues_Assigned_To]
[Issue 2 ][Kathleen Gail Jensen, Kelly Rollin,....]

However, the SQL is written as:

SELECT Issues.Title
   , Issues.AssignedTo
FROM Issues
WHERE ((
   (Issues.AssignedTo.Value)=6
   Or
   (Issues.AssignedTo.Value)=10));

Because the hidden - behind the scenes table has the information stored as:

[PK]    [Data_1]
[1]     [somename]
[...]
[6]     [Kelly Rollin]
[...]
[10]    [Lisa Miller]
[...]

+ This is not intuitive to the person writing the SQL if they are not familiar with the nature of the MVF. They are most likely going to construct a simple query based on the "string values" for each person's name.

This will hold true for updates, deletions and insertions. Hopefully, the MVF is based on a table (or maybe a SharePoint list) where the bound value is easily determined. If it's a typed list of data - well, I hope you like the users digging thru your table designs.

Speaking of this, unless you've used a table, updating your list of items will become a nightmare. For example if "Kelly Rollin" decided that her new name is "Jell Roll" or perhaps a new person is added to the group, in either case, you will have to open your data table alter the value in the lookup field, it's not best practice to alter the tables once designed and in "production". Of course, with the table you simple alter as normal.

+ There is a performance hit on the database, for each .Value added to the SQL the database has to iterate thru the dataset several times (see the above link, MS discusses this quite thoroughly) to pull the desired records. Perhaps on supper fast PCs one might not notice this; however, as many of us have found, over a lagging network, this can become an agonizingly slow and buggy experience.

Now we come to the VBA... doable...
Manipulate Multivalued Fields With DAO


I'd like to go deep here; however, I'm out of time for the moment.
-Z

[jforbes]

Z makes a lot of good points, especially:

+ The only "good" reason to ever use multivalue fields is if you're using SharePoint Services as this is the only means available to establish table relationships in the web-interface

+ Keep in mind that upgrading to a mature RDMS such as SQL-Server, MySQL, MariaDB, Oracle may very well convert a multivalue field to a Memo field, so keep upgrading in mind when making your choice

I would like to expand on this and mention a related point that has to do with maintainability. As a Company, especially a software company grows, so does thier code base. And often that software base will grow to the point that more than one person is maintaining the code. Multiple people maintaining software code is reason enough for us to stay away from Multivalue Fields as it is an MS-Access specific construct. It being specific means that people who haven't dealt with Access and/or the specific database with Multivalue Fields will have an additional and unnecessary programming construct deal with. Someone who is proficient in SQL and .NET programming can often hop in a MS-Access database and fix a problem with relatively little trouble. Were talking maintenance, no new development. But if you take the same person and ask them to maintain some code centered around a Multivalue Field, it will take them longer to get it done and will increase the likelihood of errors.

Maintainability alone is enough to cause us to stay away from Multivalue Fields. Maintainability is akin to Portability. Again, as the code base grows, some code or databases may get ported/migrated to different technologies and platforms. Sometimes this is even a fork of where the code runs and it is maintained on two or three different platforms.

Moving tables and Queries from MS-Access into SQL Sever has a few challenges as the syntax is similar but not the same, but the challenges would be compounded with the inclusion of Multivalue Fields. Then maintaining a Query in SQL along with it's original version in MS-Access gets overly difficult rendering Diff Tools less effective.

Using Diff Tools to maintain code across platforms is often highly effective and can save considerable time. So much so that we often write code so that it is as close to identical as possible across platforms. We even added a library to VB.NET that replicates some of the functionality of VBA that doesn't exist in VB.NET. The following code example resides in both VB.NET and VBA. The highlighted portions are functions that typically are only available in VBA, but with the functions being replicated in a VB.NET library, the code is identical in the VB application and MS-Access. Using a Diff Tool then becomes easy to migrate changes back and forth between applications.

    'calcCriticalNumber
    Public Function calcCriticalNumber(ByRef sNumberName As String, Optional sV1 As String = "", Optional sV2 As String = "", Optional sV3 As String = "", Optional sV4 As String = "", Optional sD1 As String = "", Optional sD2 As String = "") As Double
        On Error GoTo ErrorOut
 
        Dim bNumberFound As Boolean
        Dim sError As String
 
        Dim iCalcType As Integer
        Dim sExpression As String
        Dim sColumn As String
        Dim sFilterName As String
 
        Dim sTable As String
        Dim sWhere As String
 
        sError = ""
        sTable = ""
        sWhere = ""
        calcCriticalNumber = 0
        bNumberFound = (DCount("NumberName", "Numbers", "NumberName='" & sNumberName & "'") > 0)
        If bNumberFound Then
            iCalcType = Nz(DLookup("CalcType", "Numbers", "NumberName='" & sNumberName & "'"), 0)
            sExpression = Nz(DLookup("Expression", "Numbers", "NumberName='" & sNumberName & "'"), "")
            sColumn = Nz(DLookup("Column", "Numbers", "NumberName='" & sNumberName & "'"), sExpression)
            sFilterName = Nz(DLookup("FilterName", "Numbers", "NumberName='" & sNumberName & "'"), "")
 
            Select Case iCalcType
                Case 1
                    'TODO: Add Expressions to Critical Numbers
                Case 2, 3, 4, 5, 6 ' Non Expression
                    sTable = DLookup("BaseTable", "Filters", "FilterName='" & sFilterName & "'")
                    sWhere = buildFilterWhereClause(sFilterName, False, sV1, sV2, sV3, sV4, sD1, sD2)
                    If Len(sTable) = 0 Then sError = sError & "Table needs to be supplied.  " & vbCrLf
                    If Len(sColumn) = 0 Then sError = sError & "Column needs to be supplied.  " & vbCrLf
                Case Else
            End Select
 
            If Len(sError) = 0 Then
                Select Case iCalcType
                    Case 1 ' Expression
                        'TODO: Add Expressions to Critical Numbers
                    Case 2 ' Count
                        calcCriticalNumber = Nz(DCount(sColumn, sTable, sWhere), 0)
                    Case 3 ' Sum
                        calcCriticalNumber = Nz(DSum(sColumn, sTable, sWhere), 0)
                    Case 4 ' Average
                        calcCriticalNumber = Nz(DAvg(sColumn, sTable, sWhere), 0)
                    Case 5 ' Min
                        calcCriticalNumber = Nz(DMin(sColumn, sTable, sWhere), 0)
                    Case 6 ' Max
                        calcCriticalNumber = Nz(DMax(sColumn, sTable, sWhere), 0)
                    Case Else
                End Select
            End If
 
        Else
            sError = sError & "Could not find the Critical Number '" & sNumberName & "'" & vbCrLf
        End If
 
        If Len(sError) > 0 Then Debug.Print(sError)
        ' Throw away Error, for now.
ExitOut:
        Exit Function
 
ErrorOut:
        gErrorMessage = ""
        Call customErrorHandler(mProcedureName, True, Err, Erl, gErrorMessage)
        Resume ExitOut
    End Function

This lengthy response doesn't directly address the title of the post:

multivalue field in access, how it don't break normalization rule.

as it doesn't address normalization at all. But hopefully it will help minimize the amount of headaches people might experience by cautioning about the use of Multivalue Fields.

[hvsummer]

@ZMBD: this part you gave to resolve below request is not true:

"Searching for more than one value in a multivalued field

Sometimes, you need to search for a match on more than one value. Suppose you want to see those issues in which both "Kelly Rollin" and "Lisa Miller" are among the values in the AssignedTo field. The bound value for Kelly Rollin is 6, and the bound value for Lisa Miller is 10. To specify several criteria for a multivalued field, you can use the AND and the OR operators.
Now "Kelly Rollin" and "Lisa Miller" are shown in the table you want to search on as this:
[Issues_Title][Issues_Assigned_To]
[Issue 2 ][Kathleen Gail Jensen, Kelly Rollin,....]"

the correct SQL to find "Kelly Rollin" and "Lisa Miller" inside MVFs Table and retrieve their infomation is not that SQL.

you should use this instead:

SELECT Issues.Title
   , Issues.AssignedTo.Value
FROM Issues
WHERE ((
   (Issues.AssignedTo.Value)= "Kelly Rollin")
   Or
   (Issues.AssignedTo.Value)= "Lisa Miller"));
 

it'll return Title of each search-item each row, no need to pack it up into 1 line like MVFs in table.
because we need to see those issues in which both "Kelly Rollin" and "Lisa Miller" are among the values in the AssignedTo field, we don't have to retrieve infomation as original Format (MVFs in table which combined multi rows into 1) to see the issue.
With my approaching, you can find the issue that Assigned To "Kelly Rollin" and "Lisa Miller" without any problem.

[zmbd]

hvsummer,
Did you happen to follow the link provided in Post#4, you should have noted that the indented material, and the SQL solution I've given in Post#4 that you have quoted and said is "not true," is, in fact, from the Office technical support website dealing with how to handle MVF and it is, in my experience, the correct solution for dealing with MVF.

What you will find is that the simple changes in your query, on line 5 of the cited code in post#4 from the value "6" to "Kelly Rollin" and again a similar change on line 7 ("10" to "Lisa Miller") will not normally return the desired records because the query will be looking for the string value of "Kelly Rollin" in the stored information which is actually the value of 6 for "Kelly and 10 for "Lisa" - as stated by Microsoft themselves as well as by many experts in the field that deal with Access and other RDMS.

Unfortunately, I am all too familiar with MVF, and their madness, having inherited a few databases that people "couldn't get to work" that had dozens of MVF in the tables because these people were writing queries exactly like you have given (Post#6) because it worked for a single search parameter when they needed to be writing the query as shown in post Post#4 and again as given in the cited code for a complex search parameter.

In fact, I have the Microsoft article I've cited bookmarked because that was one of the solutions ( along with The Evils of Lookup Fields in Tables ) that allowed me to solve the problems we were having in the department... and also what eventually lead me to discover the hidden tables and "fix" the database with the proper join table(s).

However, just to see if MS hasn't fixed something in Office2013 vs Office2007 when these horrible fields were introduced, when I get a chance I will re-test the old SQLs against the MVF.

Even if MS has fixed this issue, MVF still break the 1NF rule for normalized database design.

[hvsummer]

@zmbd: there are much different between assignedto and assignedto.value.
plz read carefully in select statement.
you select Issues.AssignedTo while me select Issues.AssignedTo.Value in statement and it did make a lot of different.

if you SELECT only Issues.AssignedTo, then to find any name stored in MVFs, you have to find by number.
but, If you SELECT Issues.AssignedTo.Value, then to find any name, just pass it string into where condition.

ok, I think you did not understand, I'll add a picture here.
plz don't follow any microsoft MVFs' instruction, it's too outdate now, people can think a lot of way to manipulate thing.

here is MVFs w/o .Value

Here is MVFs.Value

so if you want to find itemname in my table, why not use MVFs.Value in select instead MVFs alone ?

[zmbd]

you select Issues.AssignedTo while me select Issues.AssignedTo.Value in statement and it did make a lot of different.

You need to very carefully review my post. You have missed something... namely, I have not used the Issues.AssignedTo in post#4 lines 5 and 7.
I have use the Issues.AssignedTo.value

plz don't follow any microsoft MVFs' instruction, it's too outdate now, people can think

1) MS's posts are up to date. They have not made any changes in the MVF implementation between its introduction in ACC2007 to ACC2013. I have just received permission to review ACC2016 so we'll see if there's any improvement.
2)I also have multiple reference documents, including some information as it relates to the newest Office2016. These documents also follow the information I have previously cited regarding the proper use of MVF fields.

ok, I think you did not understand, I'll add a picture here.

1) Do you have your pictures backward?
2) Are you trying to show me what a MVF is... I have a very good understanding of these fields.
3) In any case, I do not see where these images support anything you've tried to explain regarding the use of MVF as it applies to the 1NF requirement for database normalization at the table level. I have not argued the point that they flatten data quite nicely. What I have stated is that these fields hide the true nature of the data storage, create additional work, are not intuitive for many uses when creating complex queries, are not supported in any other database - not even MS-Azure handles these (read more), SharePoint occasionally has weird exceptions/errors when the Access recordset contains a MVF (not a simple Lookup, but a the field with multiple items as your image shows)(( for example: {"error":{"code":"-1, Microsoft.SharePoint.Client.InvalidClientQueryExce ption", Message":{"lang":"en-US","value":"An unexpected 'PrimitiveValue' node was found when readingfrom the JSON reader. A 'StartObject' node was expected."}}} )) took me two weeks to figure that one out.... the culprit was a MVF in a small table I overlooked - sigh.

-- I'll give you another reason not to use MVF/LUF at the table level; if you ever need to create a temp table using the SELECT ... INTO ... FROM ... construct, Access will toss the following error:

"Multi-valued fields are not allowed in SELECT INTO statements."

To de-normalize (flatten) data for some of the reports I run, I do have to occasionally create a temporary table. I usually do this by creating a secondary backend using this exact construct (the database is faster than vba :) ); however, that's a topic for another thread (and has been discussed in other threads a few times :) )

Finally, as promised, I've pulled out the Acc2013 test database, created the tables and links as given in the link cited in post#4. When I created the simple, single value, criteria query it returned the expected record(s); however, once I tried the multiple criteria, ACC2013 tossed the [Data type mismatch in criteria expression]. When I change the Text to the numeric value of the associated primary key, the query returns the records containing the desired values... and when using the AND operator in the criteria, one copy of the related record for each match (oh joy, say 2 criteria, 6 records match; thus the SQL returns 12 records using the AND operator), and you cannot use the DISTINCT key word with multivalued fields. Total's queries become a real nightmare here too.

[hvsummer]

@zmbd: hahah, I didnot mention about line 5 and 7, problem is 2, the 2nd line. I did say you and me are different in SELECT statement :D
now take a look back to it ==

I use this

"SELECT Issues.Title
   , Issues.AssignedTo.Value"
...

while you use this

"SELECT Issues.Title
   , Issues.AssignedTo"
....

when you query like this "select field.value from table"
you can directly find value without find by number of position like the 4th post.

[zmbd]

Ok, I took your post as regarding the entire SQL statement as it applied to the complex/compound criteria, not the displayed field.

Even if you use the [].value as the displayed field the issue with the compound criteria remains as stated in my prior post

BTW: Based on your post, I did try altering my test database to use the [].value as you have suggested for the displayed field, it did not alter any of the issues/errors when applied to the compound WHERE clause and the GROUP BY clause still has the usual issues. :) . What I did find is that [].value appears to allow the SELECT DISTINCT construct whereas the non-.value does not allow this construct - I'll have to look at that more later - I suspect it has to do with the difference in the backside table relations wherein one is referring to the hidden join table and the other is not - something for a rainy day.

There is one more issue that I have noted with this simple change from the non-.value to the [].value field...
(sorry to switch field/table names on you here, for those following along, [Table3]==[Issues] ; [IDBrand]==[AssignedTo] fitting this in between the kid's lunch and bath this afternoon and this is the database I have open :) )

SELECT  Table3.pk
    , Table3.IDBrand
FROM Table3
WHERE (((Table3.IDBrand.Value)=3))
     OR (((Table3.IDBrand.Value)=5));

Returns the record of
[PK][IDBrand]
[3][Name3,Name5]

Whereas

SELECT Table3.pk
    , Table3.IDBrand.Value
FROM Table3
WHERE (((Table3.IDBrand.Value)=3
    Or (Table3.IDBrand.Value)=5));

Returns the record of
[PK][IDBrand]
[3][Name3]
[3][Name5]

Which is a minor thing, unless you want that concatenated field. It really depends on the usage of the data, and that, if one is going to use MVF, then the 1st gives the ability to alter the returned record to add additional values from the list; whereas, the second does not provide this within the record field - nor can one add a new record with the same PK with the new item from the list provided in [IDBrand].

As a matter of semantics:
"SELECT .... FROM .... (etc)" when one uses the term "Select statement," in my experience, people in the Data Profession tend to mean that the entire SQL is being referred to not just the SELECT clause of the SQL statement.

Because of this interaction between professionals in the Data profession and myself, it has become my normal custom (especially as of late :) )when referring to specific section of the SQL statement to say/type "SELECT clause" or "FROM clause" etc, avoiding the term "statement" which has lead to many a protracted conversation (o_O) .

>OK< Kids want me to wash their hair and "squeegee them off"
Maybe I can stay dry this time? Nope /)

[hvsummer]

haha, your kid really funny.
I don't suggest anything else for using MVFs.

but In my situation, to compare the promotion price with each price of Item on daily bill, I need [].value field since it contain ID item.

other side, To manager those ID, I use non-.value as subform inside a Main form that linked row source to "table3" as our example.

in that main Form I can choose multi ID Item from MVFs and input any information come with it then me.dirty = false with 1 button,
and watch/manager them as group at subform datasheet's viewer.

I think I can create something looklike Rank query similar to the one NeoPa has posted, with each Rank is name of group Item, but it'll not let me see each item on a group. Another way is to use Childconcarnate UD-Function, but I'm not sure whether performance will drop or function can miss some ID Item or not.

so in fact, To compare value, I choose [].value (compare each id to each id M-to-M relationship group by query)
To manager them, I choose non-.value (to input and watch).

that how MVFs help me for now untill I divorce her when I found another true love lol