split field data

L

LJG

I have a table that has records as:
ref colour
000001 blu blk gre yel

but I am looking to split it so:

ref colour
000001 blu
000001 blk
000001 gre
000001 yel

anyone any ideas?

Many thanks
 
D

Douglas J. Steele

You'll have to use VBA: I don't believe it'll be possible to use SQL.

The best approach would be to create a new table that you can populate from
the existing one. Something like the following untested aircode should work
(Note that I'm using DAO: if you're using Access 2000 or 2002, you'll need
to insure that you add a reference to DAO. Note too that I'm using the Split
function, which doesn't exist in Access 97 or older)

Sub PopulateNewTable

Dim dbCurr As DAO.Database
Dim rsOldData As DAO.Recordset
Dim lngLoop As Long
Dim lngRef As Long
Dim strColour As String
Dim strSQL As String
dim varColours As Var

strSQL = "SELECT ref, colour FROM OldTable"
Set dbCurr = CurrentDb()
Set rsOldData = dbCurr.OpenRecordset(strSQL)
Do While rsOldData.EOF = False
lngRef = rsOldData!ref
strColour = rsOldData!Colour
varColours = Split(strColour, " ")
If IsNull(varColours) = False Then
For lngLoop = LBound(varColours) To UBound(varColours)
strSQL = "INSERT INTO NewTable (ref, colour) " & _
"VALUES (" & lngRef & ", '" & varColours(lngLoop) & "')"
dbCurr.Execute strSQL, dbFailOnError
Next lngLoop
End If
rsOldData.MoveNext
Loop

rsOldData.Close
Set rsOldData = Nothing
Set dbCurr = Nothing
 
L

LJG

Hi Douglas,

Thanks for that, not good at VBA, could you walk me through it please

Les

PS I have added a reference for DAO
 
J

Jörg Ackermann

LJG said:
Thanks for that, not good at VBA, could you walk me through it please
Click to expand...

a version without a temporary table,
may be a little bit easier for you...

in a standard-module create a function:
---------------------------------------------------------------------------
Public Function SplitColors(strColor As String, index As Integer) As String
Dim tmp
On Error Resume Next
tmp = split(strColor, " ")
SplitColors = tmp(index)
End Function
---------------------------------------------------------------------------


Then make a new query, open sql-view and copy in
sql-window:

(I assumed there are max. 10 several colors. If more, then
insert nessessary lines in same way)

SELECT ref, SplitColors([Color],0) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],0))>0
UNION SELECT ref, SplitColors([Color],1) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],1))>0
Union SELECT ref, SplitColors([Color],2) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],2))>0
union SELECT ref, SplitColors([Color],3) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],3))>0
UNION SELECT ref, SplitColors([Color],4) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],4))>0
UNION SELECT ref, SplitColors([Color],5) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],5))>0
UNION SELECT ref, SplitColors([Color],6) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],6))>0
UNION SELECT ref, SplitColors([Color],7) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],7))>0;
UNION SELECT ref, SplitColors([Color],8) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],8))>0
UNION SELECT ref, SplitColors([Color],9) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],9))>0;

save as qryColors and open it.

HTH
 
L

LJG

Just to be sure where to put the new table names, I have indicated >> <<were
'I' think I should be putting the new table names, OldTable = new1, NewTable
= new2

Dim dbCurr As DAO.Database
Dim >>rsnew1<< As DAO.Recordset
Dim lngLoop As Long
Dim lngRef As Long
Dim strColour As String
Dim strSQL As String
dim varColours As Variant

strSQL = "SELECT ref, colour FROM >>new1<<"
Set dbCurr = CurrentDb()
Set >>rsnew1<< = dbCurr.OpenRecordset(strSQL)
Do While >>rsnew<<.EOF = False
lngRef = >>rsnew1<<!ref
strColour = >>rsnew1<<!Colour
varColours = Split(strColour, " ")
If IsNull(varColours) = False Then
For lngLoop = LBound(varColours) To UBound(varColours)
strSQL = "INSERT INTO >>new2<< (ref, colour) " & _
"VALUES (" & lngRef & ", '" & varColours(lngLoop) & "')"
dbCurr.Execute strSQL, dbFailOnError
Next lngLoop
End If
rs>>new1<<.MoveNext
Loop
Set >>rsnew1<< = Nothing
Set dbCurr = Nothing

When I have done this, how do I run this ? is it just click the run button
in the module window

Don't mean to be thick, just need to learn

Thanks
Les
 
D

Douglas J. Steele

Copy the code into a new module. Go to the Immediate Window, type
"PopulateNewTable" and hit enter to run the code. Once you've run it, you
can go back to the module and simply not save it, as you won't need it
again.

I just noticed that I made a couple of typos in what I posted. First, the
first line of the routine should have been:

Sub PopulateNewTable()

(I forgot the parentheses)

As well, you need an End Sub at the end of the code.

Yes, you need to change the table names that I used to what your tables are
actually called. There's no need to change the name from rsOldData to
rsNew1, although there's nothing wrong with doing so if you want.
 
D

Douglas J. Steele

Not a bad idea, Jörg, but I suspect it will run extremely slowly.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Jörg Ackermann said:
LJG said:
Thanks for that, not good at VBA, could you walk me through it please
Click to expand...

a version without a temporary table,
may be a little bit easier for you...

in a standard-module create a function:
---------------------------------------------------------------------------
Public Function SplitColors(strColor As String, index As Integer) As
String
Dim tmp
On Error Resume Next
tmp = split(strColor, " ")
SplitColors = tmp(index)
End Function
---------------------------------------------------------------------------


Then make a new query, open sql-view and copy in
sql-window:

(I assumed there are max. 10 several colors. If more, then
insert nessessary lines in same way)

SELECT ref, SplitColors([Color],0) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],0))>0
UNION SELECT ref, SplitColors([Color],1) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],1))>0
Union SELECT ref, SplitColors([Color],2) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],2))>0
union SELECT ref, SplitColors([Color],3) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],3))>0
UNION SELECT ref, SplitColors([Color],4) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],4))>0
UNION SELECT ref, SplitColors([Color],5) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],5))>0
UNION SELECT ref, SplitColors([Color],6) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],6))>0
UNION SELECT ref, SplitColors([Color],7) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],7))>0;
UNION SELECT ref, SplitColors([Color],8) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],8))>0
UNION SELECT ref, SplitColors([Color],9) AS nColor FROM tblColours
WHERE Len(SplitColors([Color],9))>0;

save as qryColors and open it.

HTH
Click to expand...
 
L

LJG

Hi Douglas,

Just getting around to sorting this, thanks for your help, I have just done
as you suggested and keep getting an error message:

Run-Time '3061'

To few parameters.expected 1.

This is the line it get stuck on:

Set rsnew1 = dbCurr.OpenRecordset(strSQL)
when i hover of the highlighted area is shows:

rsnew1=nothing

Any help would be appreciated.

Thanks
Les
 
L

LJG

Further to my previous post, I have now sorted the problem, but the ref
numbers are not updating correctly e.g:

ref number 000001= 1

the first 5 digits are being dropped.

Les
 
D

Douglas J. Steele

If the reference number is numeric, it won't have leading zeroes. If it's
text, you'll need to change lngRef to strRef (change the declaration from
Dim lngReg As Long to Dim strReg As String) and put quotes around the value
of strRef, just as you are for varColours(lngLoop)
 
J

jchqkjjzhhszhx

http://www.saob.net/index.php?n=hsdy
Douglas J. Steele said:
Copy the code into a new module. Go to the Immediate Window, type
"PopulateNewTable" and hit enter to run the code. Once you've run it, you
can go back to the module and simply not save it, as you won't need it
again.

I just noticed that I made a couple of typos in what I posted. First, the
first line of the routine should have been:

Sub PopulateNewTable()

(I forgot the parentheses)

As well, you need an End Sub at the end of the code.

Yes, you need to change the table names that I used to what your tables
are actually called. There's no need to change the name from rsOldData to
rsNew1, although there's nothing wrong with doing so if you want.
Click to expand...
 
L

LJG

Hi Douglas,

Still not getting it, changed the strRef from Long to String, but were
abouts in the code do I put strRef,""?

Sorry, Iam just not getting this VBA code

Les
 
L

LJG

Were in the code do I add the strRef ""?, do I also need to add, Dim varRef?
and as for lngLoop it relates to splitting the field colours?, do I need to
add an addition lngLoop for the ref?

TIA

Current code below:
Sub PopulateNewTable()

Dim dbCurr As DAO.Database
Dim rst2t_new1 As DAO.Recordset
Dim lngLoop As Long
Dim strRef As String
Dim strColours As String
Dim strSQL As String
Dim varColours As Variant


strSQL = "SELECT ref, colours FROM t2t_new1"
Set dbCurr = CurrentDb()
Set rsOldData = dbCurr.OpenRecordset(strSQL)
Do While rsOldData.EOF = False
strRef = rsOldData!ref
strColours = rsOldData!Colours
varColours = Split(strColours, " ")
If IsNull(varColours) = False Then
For lngLoop = LBound(varColours) To UBound(varColours)
strSQL = "INSERT INTO t2t_new2 (ref, colours) " & _
"VALUES (" & strRef & ", '" & varColours(lngLoop) & "')"
dbCurr.Execute strSQL, dbFailOnError
Next lngLoop
End If
rsOldData.MoveNext
Loop

rsOldData.Close
Set rsOldData = Nothing
Set dbCurr = Nothing

End Sub
 
D

Douglas J. Steele

strSQL = "INSERT INTO t2t_new2 (ref, colours) " & _
"VALUES ('" & strRef & "', '" & varColours(lngLoop) & "')"

Exagerated for clarity, that 2nd line is:

" VALUES (' " & strRef & " ', ' " & varColours(lngLoop) & " ' ) "

The reason for varColours is so that you can store the results of the Split
function into an array. Since you only have a single value of ref per row,
there's no need to split it, therefore you don't need varRef.

One error you do have is that you changed the name of the Recordset object
in the declarations, but not in the code.

Change

Dim rst2t_new1 As DAO.Recordset

back to

Dim rsOldData As DAO.Recordset
 
Top