CODEWITHSUNDEEP
ms-access
amay
amay

Reputation: 43

how to convert one column data into multiple rows in ms access using ms access query

What I have in ms access table is below

  1. Number purchase
  2. 101 product1 product2 product3
  3. 102 product1 product2

And I want to convert it into below format using ms access query(using select query only)

  1. Number purchase
  2. 101 product1
  3. 101 product2
  4. 101 Product3
  5. 102 product1
  6. 102 product2

I am not able to the conversion column into rows ,in table i have a column in which multiple values are separated by the space and I have to convert it into row by row in ms access select query please any one can help me

Upvotes: 1

Views: 2504

Answers (1)

John
John

Reputation: 1004

If you are willing to use code then it is quite easy Use the following code to count the number of spaces

Public Function calculateSplits(InputRecord as String) 
Dim recordWithoutSpaces as String
Dim noOfSpaces as Integer

recordWithoutSpaces = Replace(InputRecord," ","")
noOfSpaces =Len(InputRecord) -Len(recordWithoutSpaces )
calculateSplits = noOfSpaces 
End Function

Then use the following code to Split the Records 

Public Function ParseText(TextIn As String, x As Byte, Optional MyDelim As String) As Variant
On Error Resume Next
If Len(MyDelim) > 0 Then
   ParseText = Split(TextIn, MyDelim)(x)
Else
   ParseText = Split(TextIn, " ")(x)
End If
End Function

Then you just create a function that reads your table and appends records to another table splitted as you want

According to your latest code 

Private Sub Command0_Click()
Dim myDelim As String
Dim strSQL As String ' want to insert the ParseText value into the new cust_info table
Dim ParseText As String
myDelim = " "
If Len(myDelim) > 0 Then
      For i = 0 To 3 ' <-- n+1 CalculateSplits e.g if you have found 3 splits (spaces) then i =0 to 4
         ParseText = Split("101 product1 product2 product3", myDelim)(i)
            strSQL = "INSERT INTO cust_info([cust_id], [cust_prods]) VALUES ('" & i + 1 & "','" & ParseText & "');"
         DoCmd.RunSQL strSQL
       Next


End If

End Sub

Just pay attention to my comment about the upper threshold of i counter.

Upvotes: 1

Related Questions