Normalizing Fields - VBA Access

MS Access permite campuri cu valori multiple, ceea ce nu este intotdeauna potrivit. In cazul in care tabelele nu sunt folosite ca atare, pentru programator nu este nici un avantaj sa aiba campuri cu valori multiple. Tot ceea ce are de facut, este sa normalizeze datele prin atomizarea campurilor.

MS Access allows multiple values fields, which is not always appropriate. If the tables are not used as it is, there is no advantage for the programmer to having fields with multiple values. All he has to do is normalize the data by atomizing the fields.


De exemplu, avem un tabel unde codificam grupurile de utilizatori.

For example, we have a table where we code the users groups.

Si un tabel cu utilizatori si grupurile din care fac parte. Un utilizator poate face parte din mai multe grupuri.

And a table with users and the groups they belong to. A user may be part of several groups.

Sursa campului GroupID arata astfel:

The source of the GroupID field looks like this:

Pentru atomizare, folositi acest cod:

For atomization, use this code:


'source:  https://code-for-vb.blogspot.com/2019/10/normalizing-fields-vba-access.html
'please to mention

Sub ColumnsAtomization()
  Dim rst_1 As Recordset
  Dim rst_2 As Recordset
  Dim str() As String
  Dim aaa   As String
  Dim i     As Integer


  CurrentDb.Execute "CREATE TABLE [Users_New] (UserID INTEGER, UserName TEXT(20), GroupID INTEGER)"
  
  Set rst_2 = CurrentDb.OpenRecordset("Users_New")
  Set rst_1 = CurrentDb.OpenRecordset("Users")
  Do
    aaa = DLookup("GroupID", "Users", "UserID = " & rst_1!UserID)
    str = Split(aaa, ", ") 'or  str = Split(aaa, "; ") - depends on regional settings
    For i = 0 To UBound(str)
        rst_2.AddNew
        rst_2!UserID = rst_1!UserID
        rst_2!UserName = rst_1!UserName
        rst_2!GroupID = str(i)
        rst_2.Update
    Next i
  
    rst_1.MoveNext
  Loop Until rst_1.EOF = True

  Set rst_1 = Nothing
  Set rst_2 = Nothing
End Sub