parse semicolon delimited table

[Deb Auer]

MS Access 2003

I have a table called tblHistory with the following fields...
HistRev MOrder HistNotes HistDate
with the below data rows. It is semicolon delimited.
I need to parse the records to a new table called tblDestination

HistRev
1;2;3
1;2

MOrder
abc123
xz123

HistNotes
Save;Submit;Cancel
Initiate;Save

HistDate
2009-06-22 01:41:16 PM;2010-06-23 02:31:16 PM;2010-08-22 04:41:16 PM
2010-06-23 02:31:16 PM;2010-08-22 04:41:16 PM


I need the new field records in tblDestination to look like


HistRev
1
2
3
1
2

MOrder
abc123
abc123
abc123
xz123
xz123

HistNotes
Save
Submit
Cancel
Initiate
Save

HistDate
2009-06-22 01:41:16 PM
2010-06-23 02:31:16 PM
2010-08-22 04:41:16 PM
2010-06-23 02:31:16 PM
2010-08-22 04:41:16 PM


Please, please help

[Rabbit]

It would probably be easiest to use VBA.
1) Step through the recordset.
2) Split() out the fields.
3) Loop through the split() result and insert records into the new table.

[gnawoncents]

I was working on the same concept and came up with the code listed below. It takes into consideration the fact that MOrder only has one entry per field. Also, it is based on the assumption that the HistRev field will always have one entry per parsed record. All you would need to add is some way to ensure you don't copy over the same records again if this is not a one-time transfer.

Private Sub btnParse_Click()
 
Dim dbsParse As Database
Dim rstParse As Recordset
Dim intCount As Integer
Dim intRecord As Integer
Dim varHistRev As Variant
Dim varMOrder As Variant
Dim varHistNotes As Variant
Dim varHistDate As Variant
Dim intCurrent As Integer
Dim strHistRev As String
Dim strMOrder As String
Dim strHistNotes As String
Dim strHistDate As String
 
strHistRev = ""
strMOrder = ""
strHistNotes = ""
strHistDate = ""
Set dbsParse = CurrentDb
Set rstParse = dbsParse.OpenRecordset("tblHistory")
 
rstParse.MoveLast
rstParse.MoveFirst
Do Until rstParse.EOF
    varHistRev = Split(rstParse![HistRev], ";")
    varMOrder = Split(rstParse![MOrder], ";")
    varHistNotes = Split(rstParse![HistNotes], ";")
    varHistDate = Split(rstParse![HistDate], ";")
    intCurrent = 0
    Do Until intCurrent = UBound(varHistRev) + 1
        strHistRev = varHistRev(intCurrent)
        If intCurrent < (UBound(varMOrder) + 1) Then strMOrder = varMOrder(intCurrent)
        If intCurrent < (UBound(varHistNotes) + 1) Then strHistNotes = varHistNotes(intCurrent)
        If intCurrent < (UBound(varHistDate) + 1) Then strHistDate = varHistDate(intCurrent)
        intCurrent = intCurrent + 1
 
        dbsParse.Execute " INSERT INTO tblDestination " _
        & "(HistRev, MOrder, HistNotes, HistDate) VALUES " _
        & "('" & strHistRev & "','" & strMOrder & "','" & strHistNotes & "','" & strHistDate & "');"
    Loop
rstParse.MoveNext
Loop
 
Set rstParse = Nothing
dbsParse.Close
 
End Sub

[NeoPa]

Nice work gnawoncents.

The OP may need to determine if [HistRev] is numeric or not and change the SQL accordingly. This is possibly also true for [HistDate], but for Date/Time rather than numeric. [MOrder] appears to be single items only, so that may need a little minor tweaking. rstParse should probably be closed prior to setting to Nothing, and dbsParse would never be closed (as it's a pointer to the current database), but she can presumably sort that out now she has a pretty full template solution to work from. Considering you were working on a database in your head that's not bad going (All the points are pretty minor and usually get picked up when testing anyway, when it's hands-on).