[AccessD] Parsing fields to normalize data

William,

    Sample subroutine given below should be able to get the needful done.

    T_A is the source table with fields with fields named EMSID (number type), Booth_1, Booth_2 and Booth_3 (all text type), while T_B is the target table with fields EMSID (number type) and BoothNum (text type).

    The procedure will take care of all fields in series identified by "Booth_" in the source table (i.e. even if more or less than three).

Best wishes,
A.D.Tejpal
--------------

====================================
Sub Normalize()
    Dim rst1 As DAO.Recordset, rst2 As DAO.Recordset
    Dim fd As DAO.Field
    Dim Txt As String, Rtv As Variant, Cnt As Long
    
    Set rst1 = CurrentDb.OpenRecordset("T_A")
    Set rst2 = CurrentDb.OpenRecordset("T_B")
    
    Do While Not rst1.EOF
        For Each fd In rst1.Fields
            If Left(fd.Name, 6) = "Booth_" Then
                Txt = fd.Value
                Txt = Replace(Txt, Space(1), "")
                Rtv = Split(Txt, "/")
                For Cnt = LBound(Rtv) To UBound(Rtv)
                    With rst2
                        .AddNew
                        .Fields("EMSID") = rst1.Fields("EMSID")
                        .Fields("BoothNum") = Rtv(Cnt)
                        .Update
                    End With
                Next
            End If
        Next
        rst1.MoveNext
    Loop
    
    rst1.Close
    rst2.Close
    Set rst1 = Nothing
    Set rst2 = Nothing
    Set fd = Nothing
End Sub
====================================

  ----- Original Message ----- 
  From: William Hindman 
  To: Access Developers discussion and problem solving 
  Sent: Monday, September 26, 2005 05:42
  Subject: [AccessD] Parsing fields to normalize data


  ..given a record containing the following data: (from a legacy DOS app)

  field name: EMSID
  field type: long
  record data: 44318

  field name: Booth Number or numbers (#1)
  field type: text
  record data: 649/651/   748/   750

  field name: Booth Number or numbers (#2)
  field type: text
  record data: 653/655/   657/   659/   752/   754/   756/   1358

  field name: Booth Number or numbers (#3)
  field type: text
  record data: DF-7/DF-8

  ..I need to make a new table with the following data structure: (sort order 
  is immaterial)

  EMSID    BoothNum
  44318    649
  44318    651
  44318    748
  44318    750
  44318    653
  44318    655
  44318    657
  44318    659
  44318    752
  44318    754
  44318    756
  44318    1358
  44318    DF-7
  44318    DF-8

  ..I know I've done this before but it was back in the days when my brain 
  cells weren't full of plaque :(
  ..I've racked my remaining shreds and tried dozens of supposed solutions 
  and am now throwing things at the wall ...some stick :(
  ..will some poor soul here take mercy on me and end my misery ...other than 
  by shooting me?

  William ...working waaaaaay too late on a Sunday after watching my Dolphins 
  win today by the hair in their teeth :)