Access DB Query to split the columns delimited by "," to multiple rows

Question

I am using Access DB and the data in the table are as follows

ID  Number
1   12,34,45,55,67,66,5,7
2   45,55,67,89,777
3   23,45,67,88,777,8888,564
4   1,234,567,890,987,650,000,000
5   222,233,345,645,634,000
6   33,44,55,66,77
7   12,34,22,88,99

The expected output that I am looking

I have accessdb and no other DB. requesting you to help me out with this issue as I am not aware of Accessdb. do let me know if you need any more details.

This is the query that I had written in Access db query so what it would do is that based on the number Id was getting updated in another table.

SELECT  t1.Id
        , t2.NUMBER 
FROM    table 1 AS t1 INNER JOIN table2 AS t2 ON t1.number LIKE t2.number & '*';

Hi, could you let us see the SQL or VBA code you've tried already please? — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Jun 26, 2018 at 10:26
This is the query that i had written in access db query so what it would do is that based on the number Id was getting updated in another table. SELECT t1.Id, t2.NUMBER FROM table 1 AS t1 INNER JOIN table2 AS t2 ON t1.number LIKE t2.number & '*'; — Ishan
– Ishan, Commented Jun 26, 2018 at 10:30

Erik A · Accepted Answer · 2018-06-26 11:11:35Z

This is a pretty complex task, but it can be achieved by creating a VBA function to get an item at a specific position, and then cross-joining either a pre-filled consecutive sequence table, or a sequence-generating query.

First, we declare the VBA function (from here, and I tell you why you can't use SQL for that there too):

Public Function SplitString(str As String, delimiter As String, count As Integer) As Variant
    Dim strArr() As String
    strArr = Split(str, delimiter, count + 1)
    count = count - 1 'zero-based
    If UBound(strArr) >= count Then
        SplitString = strArr(count)
    End If
End Function

Then, we use that with the sequence generating table/query to split the strings. In my example, I'll use this query by Gustav as sequence generator:

SELECT MyTable.ID,  SplitString(MyTable.[Number], ",", Sequence.[Value]) As [Number]
FROM 
    MyTable,
    (
        SELECT DISTINCT 
        [Tens]+[Ones] + 1 AS [Value], 
        10*Abs([Deca].[id] Mod 10) AS Tens, 
        Abs([Uno].[id] Mod 10) AS Ones
        FROM 
            msysobjects AS Uno, 
            msysobjects AS Deca;
    ) As Sequence
WHERE SplitString(MyTable.[Number], ",", Sequence.[Value]) Is Not Null

Zack · Accepted Answer · 2018-06-26 13:14:51Z

I think the obvious initial question is why you're storing multiple "numbers" in one field: This goes against the whole point of using a relational database (even Access). Is there a reason you're storing comma-delimited values in one field? Storing each "number" in a separate field would certainly make your queries easier to construct.

Another question: Some of the numbers stored are "000". Are these valid codes, or do they signify no value (i.e. null)? How would they be handled in your output?

I think you might be better off, if possible, to create 2 separate tables: One to store the ID (and other associated attributes), and another table to store IDs for each entity. For example: tEntity could store each entity's ID (and name). tEntityValues would have a separate row for each number/code tied to a an entity. Then, your queries would be much simpler.

Collectives™ on Stack Overflow

Access DB Query to split the columns delimited by "," to multiple rows

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related