Split single text field of one records into multiple records

[tnguyen315]

Hello,
I'm using access 2007 and I have a table called "tblArea", this table has 2 text fields named "Code" and "Area"
Results:

Code Area
1 15
1 27&48
2 37 & 52
3 45A, 51

My question is how I can split the area field into multiple records such as:

Code Area
1 15
1 27
1 48
2 37
2 52
3 45A
3 51

Thank you very much for your help, I'm very appreciated.
Twee

 

[Andrzejek]

Is the & and a , (comma) the only delimiters?
How do you know how to Split Area field?

Have fun.

---- Andy

 

[tnguyen315]

Split record when a field value contains "&" and "," (comma)
Thanks.

 

[PHV]

What about this ?

SELECT Code, Area FROM tblArea WHERE Area NOT Like '*&*' AND Area NOT Like '*,*'
UNION SELECT Code, Trim(Left(Area,InStr(Area,'&')-1)) FROM tblArea WHERE Area Like '*&*'
UNION SELECT Code, Trim(Mid(Area,InStr(Area,'&')+1)) FROM tblArea WHERE Area Like '*&*'
UNION SELECT Code, Trim(Left(Area,InStr(Area,',')-1)) FROM tblArea WHERE Area Like '*,*'
UNION SELECT Code, Trim(Mid(Area,InStr(Area,',')+1)) FROM tblArea WHERE Area Like '*,*'

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

 

[Andrzejek]

How about this:

Dim strSQL As String
Dim conn As Connection
Dim rst As Recordset
Dim aryArea
Dim i As Integer
[green]
'Pick records needed to be messed with[/green]
strSQL = "SELECT Code, Area FROM tblMyTable " _
    & " WHERE Area LIKE '*&*' " _
    & "    OR Area LIKE '*,*'"

rst.Open strSQL, conn

Do While Not rst.EOF
    Select Case True
        Case InStr(rst!Area, "&")[green]
            'Split by &[/green]
            aryArea = Split(rst!Area, "&")
        Case InStr(rst!Area, ",")[green]
            'Split by comma[/green]
            aryArea = Split(rst!Area, ",")
    End Select
    
    For i = LBound(aryArea) To UBound(aryArea)
        aryArea(i) = Trim(aryArea(i))
        strSQL = "INSERT INTO tblMyTable (Code, Area) " _
            & " VALUES(" & rst!Code & ", '" & aryArea(i) & "')"
        conn.Execute strSQL
    Next i
    
    strSQL = "DELETE FROM tblMyTable " _
        & " WHERE Code = " & rst!Code _
        & " AND Area = '" & rst!Area & "'"
    cobnn.Execute strSQL
    
    rst.MoveNext
Loop

rst.Close

Have fun.

---- Andy

 

[tnguyen315]

Thanks PHV and Andrzejek for your kind respond. I haven't tested on Andrzejek's code yet but I will.

PHV, I tried your code it's worked now however maximum i have values in record up to 4 (such as 4,5,15a,58 or 2&3 & 6 & 18).

So please modify your code. I tried to modify them but I couldn't get it work.

Again, thank you very much for your help.
Twee

 

[Andrzejek]

PHV's code will NOT change your data in tblArea table, mine will DO that. So be carefull what you want to do....

Have fun.

---- Andy

 

[tnguyen315]

Thanks Andrzejek.
I definitely do not want to delete the table and re-create the new one.
I just wanted to run the query to split the value fieds of a single record into mulitple records to run the reports.
Again, thanks alots.
I'm so appreciated for your help.
Twee

 

[Andrzejek]

My code will NOT delete the table, it just deletes a record after inserting the records with individual Areas.

So the questions is: Do you want to 'fix' the records in your tblArea table once and have one area per record from now on, or do you want to just query of what you have WITHOUT changing anything in your data base?

Do you want this:[tt][blue]
Code Area
1 15
1 27
1 48
2 37
2 52
3 45A
3 51[/blue][/tt]
to be how your tblArea looks like? Or is it just the outcome of the query?

Two different 'animals'

Have fun.

---- Andy

 

[tnguyen315]

Andrzejek, thanks again.
I don't want to change anything on the table, data or structure...or even do not need to fix the records values.
I just need to create a query to split them into multiple so I can run my reports.
Again, thank you so much for your help.
Twee

 

[Andrzejek]

If that's the case, DO NOT use my code since it will insert some new records and delete old ones, and that's NOT what you want.

You have to work with PHV's code and modify it to your needs.

Have fun.

---- Andy

 

[tnguyen315]

Andrzejek, thank you very much for your time.
I did copy your code and will test it for my future uses, maybe someday I'll need it.
Again, thanks.
I'm very appreciated.
Take care,
Thuy