parse semicolon delimited table

[auerd]

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

 

[PHV]

In a standard code module create the following function:

Public Function mySplit(str, delim, n)
On Error Resume Next
mySplit = Split(str, delim)(n - 1)
End Function

And now the parse query (SQL code)

SELECT mySplit(HistRev,';',1) AS myHistRev,MOrder,mySplit(HistNotes,';',1) AS myHistNotes,mySplit(HistDate,';',1) AS myHistDate
FROM tblHistory
UNION ALL SELECT mySplit(HistRev,';',2),MOrder,mySplit(HistNotes,';',2),mySplit(HistDate,';',2)
FROM tblHistory WHERE HistRev LIKE '*;*'
UNION ALL SELECT mySplit(HistRev,';',3),MOrder,mySplit(HistNotes,';',3),mySplit(HistDate,';',3)
FROM tblHistory WHERE HistRev LIKE '*;*;*'
ORDER BY 2,1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886