Separating rows

[chaddy]

i have table for example of 2 fields. 1st field contains "1;2;3;4;5;6" and 2nd field contains "Example". The question is can the 1st field be separated in a list box or combo box to display the rows as "1 Example" "2 Example" 3 Example", etc etc ??? thanks, paul.

 

[MajP]

This seems silly. Why do you have a field like 1;2;3;4;5;6? What are you really trying to accomplish? Sure seems a whole lot easier to have normalized data.

Field 1 Field2
1 Example
2 Example
3 Example
4 Example
5 Example
6 Example

Data within a field that represents multiple pieces of information (1;2;3;4;5;6) is just a bad idea. You will always have to come up with work arounds.

 

[chaddy]

There is too much data to normalize! so a work around would be the answer, if there is one.

Thanks

Paul

 

[MajP]

There is too much data to normalize!

There is no such thing.

You can normalize through code or through SQL, not by hand.

Do you want to do this for one row or multiple rows?

1;2;3 Example
7;8;9 Example2

Field 1 Field2
1 Example
2 Example
3 Example
7 Example 2
8 Example 2
9 Example 2

 

[chaddy]

normalise through code or SQL, if its possible.?

Thanks.

 

[MajP]

Something like

TblExample
ID field1 field2 Field3 Field4
1 1;2;3;4;5;6 Example other data abc
2 7;8;9 Example2 other data 2 efg

make tblExample 2 by copying the structure only

Public Sub normalizeData(tblOne As String, tblTwo As String, fldName As String)
  Dim rsIn As DAO.Recordset
  Dim rsOut As DAO.Recordset
  Dim fld As DAO.Field
  Dim aSplitVals() As String
  Dim splitVal As Variant
  Set rsIn = CurrentDb.OpenRecordset(tblOne)
  Set rsOut = CurrentDb.OpenRecordset(tblTwo, dbOpenDynaset)
  Do While Not rsIn.EOF
    aSplitVals = Split(rsIn.Fields(fldName), ";")
    For Each splitVal In aSplitVals
      'Debug.Print splitVal
       rsOut.AddNew
       rsOut.Update
       For Each fld In rsIn.Fields
         If Not fld.Name = "ID" Then
           rsOut.Edit
             rsOut.Fields(fld.Name) = fld.Value
           rsOut.Update
           rsOut.Edit
             rsOut.Fields(fldName) = splitVal
           rsOut.Update
           End If
        Next fld
        rsOut.MoveNext
    Next splitVal
    rsIn.MoveNext
  Loop
End Sub

call normalizeData("tblExample","tblExample2","field1")

output

ID field1 field2 Field3 Field4
158 1 Example other data abc
159 2 Example other data abc
160 3 Example other data abc
161 4 Example other data abc
162 5 Example other data abc
163 6 Example other data abc
164 7 Example2 other data 2 efg
165 8 Example2 other data 2 efg
166 9 Example2 other data 2 efg

 

[chaddy]

thanks for that MajP, tested and works a treat, but, just to take it 1 step further, can it be done for multiple rows.?? (sorry, missed that bit on your last post).

eg.

ID field1 field2 Field3 Field4
1 1;2;3;4;5 Example other data 6;7;8;9;0

would give:-

ID field1 field2 Field3 Field4
1 1 Example other data 6
2 2 Example other data 7
3 3 Example other data 8
4 4 Example other data 9
5 5 Example other data 0

Thanks

Paul

 

[chaddy]

can anyone answer the above question?

 

[MajP]

That really violates normalization. Multiple values in a field related to multiple values in another field by position. As you have learned that is a really bad design. You may want to read up on relational database design, to avoid these problems in the future.


If you start with something like this

Fld1 Fld2 Fld3 Fld4 Fld5
data1 1;2;3 a;b;c data2 cat;dog;bird

The original code will put in a new table the following
Fld1 Fld2 Fld3 Fld4 Fld5
data1 1 a;b;c data2 cat;dog;bird
data1 2 a;b;c data2 cat;dog;bird
data1 3 a;b;c data2 cat;dog;bird

Now on the new table run the following code twice. First time pass in Fld3, next time pass in Fld5

Public Sub distributeValues(tbl As String, fldName As String)
  Dim rs As DAO.Recordset
  Dim aSplitVals() As String
  Dim splitVal As Variant
  Dim strVal As String
  Dim intCount As Integer
  Set rs = CurrentDb.OpenRecordset(tbl)
  Do While Not rs.EOF
    strVal = rs.Fields(fldName)
    aSplitVals = Split(rs.Fields(fldName), ";")
    For intCount = LBound(aSplitVals) To UBound(aSplitVals)
        rs.Edit
             rs.Fields(fldName) = aSplitVals(intCount)
        rs.Update
        If Not intCount = UBound(aSplitVals) Then
          rs.MoveNext
        End If
    Next intCount
    rs.MoveNext
  Loop
End Sub

Results
Fld1 Fld2 Fld3 Fld4 Fld5
data1 1 a data2 cat
data1 2 b data2 dog
data1 3 c data2 bird

 

[chaddy]

thank you MajP, you have been a great help. Unfortunately i've inherited the data, so this code will fix the data. Thanks again.

 

[MajP]

Good luck. This works with my little test data, but any problems in the data may be problematic. Examples

a;b,c;d (error due to comma)
a;b;c;d 1;2;3 (error due to number of elements not matching)
a;b;c; (erro due to extra semi)