By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,972 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,972 IT Pros & Developers. It's quick & easy.

MS Access Tokenise Cells and Append to table to flatten out

P: n/a
Hi,

I'm just wondering if something like this is possible to either write
queries or VBA code to do.
I need to tokenise the [Relationship] and then flatten out this file.
tblBefore
------------------------------------------------------------
ID | Comp | Charge | RelationshipID
-------------------------------------------------------------
103 | AAA | Null | Null
104 | AAA | Null | Null
105 | AAA | Null | Null
106 | AAA | Rental | 103
107 | AAA | OnceOff | 104, 103
108 | AAA | Reccur | 105
tblAfter
-----------------------------------------------------
RelationshipID | Charge | ID
----------------------------------------------------
103 | Rental | 106
104 | OnceOff | 107
103 | OnceOff | 107
105 | Reccur | 108
Any ideas would be great!

Thanks in advance.

Oct 19 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Try this:
Sub ChangeTable()
Dim DB As Database
Dim RSBef As Recordset
Dim RSAft As Recordset
Dim strSelectSQL As String
Dim strRelID As String
DoCmd.SetWarnings True

strSelectSQL = "Select * FROM tblBefore " & _
"WHERE NOT Isnull(Charge) AND " & _
" Not Isnull(RelationshipID) ;"
Set DB = CurrentDb
Set RSBef = DB.openrecordset(strSelectSQL)
Set RSAft = DB.openrecordset("tblAfter", dbopendynaset)

RSBef.MoveFirst
Do While Not RSBef.EOF
If Len(RSBef!RelationshipID) = 3 Then
RSAft.AddNew
RSAft!ID = RSBef!ID
RSAft!Charge = RSBef!Charge
RSAft!RelationshipID = RSBef!RelationshipID
RSAft.Update
RSBef.MoveNext
Else
strRelID = RSBef!RelationshipID
Do While Len(strRelID) >= 3
RSAft.AddNew
RSAft!ID = RSBef!ID
RSAft!Charge = RSBef!Charge
RSAft!RelationshipID = Left(strRelID, 3)
RSAft.Update
strRelID = Mid(strRelID, 6)
Loop
RSBef.MoveNext
End If
Loop
Set RSBef = Nothing
Set RSAft = Nothing
Set DB = Nothing
End Sub

<ja********@gmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...
Hi,

I'm just wondering if something like this is possible to either write
queries or VBA code to do.
I need to tokenise the [Relationship] and then flatten out this file.
tblBefore
------------------------------------------------------------
ID | Comp | Charge | RelationshipID
-------------------------------------------------------------
103 | AAA | Null | Null
104 | AAA | Null | Null
105 | AAA | Null | Null
106 | AAA | Rental | 103
107 | AAA | OnceOff | 104, 103
108 | AAA | Reccur | 105
tblAfter
-----------------------------------------------------
RelationshipID | Charge | ID
----------------------------------------------------
103 | Rental | 106
104 | OnceOff | 107
103 | OnceOff | 107
105 | Reccur | 108
Any ideas would be great!

Thanks in advance.

Oct 19 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.