Create Multiple Columns in a Table 1

[Jimmy24]

Dear Sir or Madam,

Thank you in advance for your assistance. I have 1 table A in Microsoft access. This table contain only 3 columns. In the table the data reads like

Here what I have

LOGID BTID MESSAGE
25 91 SEQ_MEMB_ID: 7836648; RSTORE_SEQ_ID: 7192656;

I need to create a new table using TABLE A like this:

LOGID BTID SEQ_MEMB_ID RSTORE_SEQ_ID
25 91 7836648 7192656


How can I do that...

Thanks for your assitance

Jimmy

 

[MajP]

Public Function getSEQID(varMessage As Variant) As Variant
 'not sure of your desired return type maybe a long or text so I used variant

  Dim aMessage() As String
  
  If Not Trim(varMessage & " ") = "" Then
     varMessage = Replace(varMessage, "SEQ_MEMB_ID:", "")
     varMessage = Replace(varMessage, "RSTORE_SEQ_ID:", "")
     aMessage = Split(varMessage, ";")
     getSEQID = Trim(aMessage(0))
 End If

End Function

Public Function getRSTORID(varMessage As Variant) As Variant
 'not sure of your desired return type maybe a long or text so I used variant
  Dim aMessage() As String
  
  If Not Trim(varMessage & " ") = "" Then
     varMessage = Replace(varMessage, "SEQ_MEMB_ID:", "")
     varMessage = Replace(varMessage, "RSTORE_SEQ_ID:", "")
     aMessage = Split(varMessage, ";")
     getRSTORID = Trim(aMessage(1))
  End If

End Function

sql

SELECT tableA.LOGID, tableA.BTID, getSEQID([MESSAGE]) AS SEQ_MEMB_ID, getRSTORID([MESSAGE]) AS RSTORE_SEQ_ID
FROM tableA;

results

LOGID BTID SEQ_MEMB_ID RSTORE_SEQ_ID
25 91 7836648 7836648