Split one field into Multiple ROW

  • Thread starter Thread starter Alexandre Saillant
  • Start date Start date
A

Alexandre Saillant

Hello,

I am about to acheive a dream project but one obstacle is preventing
me from ultimate success. I really need professional help here!

I have a table that look like this:

ID ,Line ,Code
001 ,A ,S42
002 ,A B C ,S51 S51 S52
003 ,A B ,S33 S32

I want to split the field "line" and "code" into rows to be conform
with a real data base. This must be done automaticaly. So it need to
look like this after the modification:

ID ,Line ,Code
001 ,A ,S42
002 ,A ,S51
002 ,B ,S51
002 ,C ,S52
003 ,A ,S33
003 ,B ,S32

Basicaly, I need to split the field into rows and not into other
fields!!!

Can anyone help me with this?

Alex
 
Dear Alex:

The general approach I'd suggest would be a UNION of a set of queries.

Write a query that returns the first piece:

001 ,A ,S42
002 ,A ,S51
003 ,A ,S33

Then another query that returns the second piece, omitting rows that
don't have a second piece:

002 ,B ,S51
003 ,B ,S32

Finally, the same for the third (and last?) piece:

002 ,C ,S52

If there could be more than 3 pieces, you need to know how many are
possible now, and perhaps in the future if it will run for data as yet
unknown.

You can use the INSTR function and SUBSTR (for Jet databases) to split
up the strings by finding spaces (I think that's what separate the
data, right?) An alternative is to write a function that finds the
Nth piece out of the string. If you have more than 3 possible pieces,
I'd recommend this strongly. It will be messy in the query for more
than 3 pieces (prediction).

You can create this UNION and append to your table from that, or
append each piece. The result will be the same except that UNION
eliminates duplicates. There may be no duplicates, or you may not
want to ignore them. That's a piece of research for your decision.

What's up with the leading commas? Would you maybe be better off in
the long run without them?

Not a bad project, I'd think. Its a good thing the way you put it:
"a real database." The change you propose is a pretty good idea, for
sure, assuming you understand the meaning of the mess you're being
given.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi Alex,

Pardon me for butting in, but the
following function (adapted from
a function Dirk provided on this ng)
should be a big help (if you are using
Access 200x) as Tom mentioned.

Save the following function in a module.

'*** start code ****
Public Function fNthElement(KeyString As Variant, _
Delimiter As String, _
ByVal ElementNo As Integer) As Variant
Dim arrSegments As Variant

arrSegments = Split(KeyString, Delimiter, -1, vbTextCompare)
If ((ElementNo - 1) <= UBound(arrSegments)) _
And (ElementNo > 0) Then
fNthElement = arrSegments(ElementNo - 1)
Else
fNthElement = Null
End If

End Function
'*** end code ***

Then (if you will only have max of
three elements in your fields), the following
query should do the job.

SELECT tblCode.ID,
fNthElement([Line]," ",1) AS SplitLine,
fNthElement(
Code: 
," ",1) AS SplitCode
FROM tblCode
UNION ALL
SELECT tblCode.ID,
fNthElement([Line]," ",2),
fNthElement([Code]," ",2)
FROM tblCode
WHERE
fNthElement([Line]," ",2) IS NOT NULL
AND
fNthElement([Code]," ",2) IS NOT NULL
UNION ALL
SELECT tblCode.ID,
fNthElement([Line]," ",3),
fNthElement([Code]," ",3)
FROM tblCode
WHERE
fNthElement([Line]," ",3) IS NOT NULL
AND
fNthElement([Code]," ",3) IS NOT NULL
ORDER BY ID, SplitLine, SplitCode;

Ah...the power of SQL!

Good luck and apologies again for butting in,

Gary Walter
 
Thank you,

Since I am not really good in Programmation and SQL, I was unable to
reproduce what you explained, I had a few bugs trying to do so.
However, you SQL statement inspired me and I came up with somekind of
a thing that seem to work:
---------SQL-----------------
SELECT conv3.apptID,
left([LC],1) AS Line,
left([DISP],4) AS Dispatch
FROM conv3

UNION ALL

SELECT conv3.apptID,
Mid([LC],5,1),
Mid([DISP],8,4)
FROM conv3

ORDER BY apptID, Line, Dispatch;
---------SQL---------------------

Note: there is four space between the caraters and YES there is more
than 3 possible lines. Can be up to 10 or so! All I have to do is to
reproduce the Mid([LC])...) things for up to 10, and second query to
clear the null fields and it's done!

But I still have a problem!!! Because I was trying to save time in my
first email, I did not include a third Field called [EST] and when I
try to put this in the SQL statement, it does not work!!!!!!!

This is what I cam up with....
-----------SQL----------------------
SELECT conv3.apptID,
left([LC],1) AS Line,
left([DISP],4) AS Dispatch
left([EST],3) AS Estimate
FROM conv3

UNION ALL

SELECT conv3.apptID,
Mid([LC],5,1),
Mid([DISP],8,4)
Mid([EST],7,3)
FROM conv3


ORDER BY apptID, Line, Dispatch, estimate;
-----------------SQL---------------------------

Why do I get someking of TYPO error message with this?????










Gary Walter said:
Hi Alex,

Pardon me for butting in, but the
following function (adapted from
a function Dirk provided on this ng)
should be a big help (if you are using
Access 200x) as Tom mentioned.

Save the following function in a module.

'*** start code ****
Public Function fNthElement(KeyString As Variant, _
Delimiter As String, _
ByVal ElementNo As Integer) As Variant
Dim arrSegments As Variant

arrSegments = Split(KeyString, Delimiter, -1, vbTextCompare)
If ((ElementNo - 1) <= UBound(arrSegments)) _
And (ElementNo > 0) Then
fNthElement = arrSegments(ElementNo - 1)
Else
fNthElement = Null
End If

End Function
'*** end code ***

Then (if you will only have max of
three elements in your fields), the following
query should do the job.

SELECT tblCode.ID,
fNthElement([Line]," ",1) AS SplitLine,
fNthElement(
Code: 
," ",1) AS SplitCode
FROM tblCode
UNION ALL
SELECT tblCode.ID,
fNthElement([Line]," ",2),
fNthElement([Code]," ",2)
FROM tblCode
WHERE
fNthElement([Line]," ",2) IS NOT NULL
AND
fNthElement([Code]," ",2) IS NOT NULL
UNION ALL
SELECT tblCode.ID,
fNthElement([Line]," ",3),
fNthElement([Code]," ",3)
FROM tblCode
WHERE
fNthElement([Line]," ",3) IS NOT NULL
AND
fNthElement([Code]," ",3) IS NOT NULL
ORDER BY ID, SplitLine, SplitCode;

Ah...the power of SQL!

Good luck and apologies again for butting in,

Gary Walter


[QUOTE="Tom Ellison"]
Dear Alex:

The general approach I'd suggest would be a UNION of a set of queries.

Write a query that returns the first piece:

001 ,A ,S42
002 ,A ,S51
003 ,A ,S33

Then another query that returns the second piece, omitting rows that
don't have a second piece:

002 ,B ,S51
003 ,B ,S32

Finally, the same for the third (and last?) piece:

002 ,C ,S52

If there could be more than 3 pieces, you need to know how many are
possible now, and perhaps in the future if it will run for data as yet
unknown.

You can use the INSTR function and SUBSTR (for Jet databases) to split
up the strings by finding spaces (I think that's what separate the
data, right?)  An alternative is to write a function that finds the
Nth piece out of the string.  If you have more than 3 possible pieces,
I'd recommend this strongly.  It will be messy in the query for more
than 3 pieces (prediction).

You can create this UNION and append to your table from that, or
append each piece.  The result will be the same except that UNION
eliminates duplicates.  There may be no duplicates, or you may not
want to ignore them.  That's a piece of research for your decision.

What's up with the leading commas?  Would you maybe be better off in
the long run without them?

Not a bad project, I'd think.  Its a good thing the way you put it:
"a real database."  The change you propose is a pretty good idea, for
sure, assuming you understand the meaning of the mess you're being
given.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
[/QUOTE][/QUOTE]
Click to expand...
 
Hi Alex,

I believe you are just missing comma(s)
after Dispatch fields:

-----------SQL----------------------
SELECT conv3.apptID,
left([LC],1) AS Line,
left([DISP],4) AS Dispatch,
left([EST],3) AS Estimate
FROM conv3

UNION ALL

SELECT conv3.apptID,
Mid([LC],5,1),
Mid([DISP],8,4),
Mid([EST],7,3)
FROM conv3


ORDER BY apptID, Line, Dispatch, estimate;

Alexandre Saillant said:
Thank you,

Since I am not really good in Programmation and SQL, I was unable to
reproduce what you explained, I had a few bugs trying to do so.
However, you SQL statement inspired me and I came up with somekind of
a thing that seem to work:
---------SQL-----------------
SELECT conv3.apptID,
left([LC],1) AS Line,
left([DISP],4) AS Dispatch
FROM conv3

UNION ALL

SELECT conv3.apptID,
Mid([LC],5,1),
Mid([DISP],8,4)
FROM conv3

ORDER BY apptID, Line, Dispatch;
---------SQL---------------------

Note: there is four space between the caraters and YES there is more
than 3 possible lines. Can be up to 10 or so! All I have to do is to
reproduce the Mid([LC])...) things for up to 10, and second query to
clear the null fields and it's done!

But I still have a problem!!! Because I was trying to save time in my
first email, I did not include a third Field called [EST] and when I
try to put this in the SQL statement, it does not work!!!!!!!

This is what I cam up with....
-----------SQL----------------------
SELECT conv3.apptID,
left([LC],1) AS Line,
left([DISP],4) AS Dispatch
left([EST],3) AS Estimate
FROM conv3

UNION ALL

SELECT conv3.apptID,
Mid([LC],5,1),
Mid([DISP],8,4)
Mid([EST],7,3)
FROM conv3


ORDER BY apptID, Line, Dispatch, estimate;
-----------------SQL---------------------------

Why do I get someking of TYPO error message with this?????










"Gary Walter" <[email protected]> wrote in message
Hi Alex,

Pardon me for butting in, but the
following function (adapted from
a function Dirk provided on this ng)
should be a big help (if you are using
Access 200x) as Tom mentioned.

Save the following function in a module.

'*** start code ****
Public Function fNthElement(KeyString As Variant, _
Delimiter As String, _
ByVal ElementNo As Integer) As Variant
Dim arrSegments As Variant

arrSegments = Split(KeyString, Delimiter, -1, vbTextCompare)
If ((ElementNo - 1) <= UBound(arrSegments)) _
And (ElementNo > 0) Then
fNthElement = arrSegments(ElementNo - 1)
Else
fNthElement = Null
End If

End Function
'*** end code ***

Then (if you will only have max of
three elements in your fields), the following
query should do the job.

SELECT tblCode.ID,
fNthElement([Line]," ",1) AS SplitLine,
fNthElement(
Code: 
," ",1) AS SplitCode
FROM tblCode
UNION ALL
SELECT tblCode.ID,
fNthElement([Line]," ",2),
fNthElement([Code]," ",2)
FROM tblCode
WHERE
fNthElement([Line]," ",2) IS NOT NULL
AND
fNthElement([Code]," ",2) IS NOT NULL
UNION ALL
SELECT tblCode.ID,
fNthElement([Line]," ",3),
fNthElement([Code]," ",3)
FROM tblCode
WHERE
fNthElement([Line]," ",3) IS NOT NULL
AND
fNthElement([Code]," ",3) IS NOT NULL
ORDER BY ID, SplitLine, SplitCode;

Ah...the power of SQL!

Good luck and apologies again for butting in,

Gary Walter


[QUOTE="Tom Ellison"]
Dear Alex:

The general approach I'd suggest would be a UNION of a set of queries.

Write a query that returns the first piece:

001 ,A ,S42
002 ,A ,S51
003 ,A ,S33

Then another query that returns the second piece, omitting rows that
don't have a second piece:

002 ,B ,S51
003 ,B ,S32

Finally, the same for the third (and last?) piece:

002 ,C ,S52

If there could be more than 3 pieces, you need to know how many are
possible now, and perhaps in the future if it will run for data as yet
unknown.

You can use the INSTR function and SUBSTR (for Jet databases) to split
up the strings by finding spaces (I think that's what separate the
data, right?)  An alternative is to write a function that finds the
Nth piece out of the string.  If you have more than 3 possible pieces,
I'd recommend this strongly.  It will be messy in the query for more
than 3 pieces (prediction).

You can create this UNION and append to your table from that, or
append each piece.  The result will be the same except that UNION
eliminates duplicates.  There may be no duplicates, or you may not
want to ignore them.  That's a piece of research for your decision.

What's up with the leading commas?  Would you maybe be better off in
the long run without them?

Not a bad project, I'd think.  Its a good thing the way you put it:
"a real database."  The change you propose is a pretty good idea, for
sure, assuming you understand the meaning of the mess you're being
given.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On 16 Dec 2003 14:26:04 -0800, [email protected] (Alexandre
Saillant) wrote:

Hello,

I am about to acheive a dream project but one obstacle is preventing
me from ultimate success.  I really need professional help here!

I have a table that look like this:

ID     ,Line     ,Code
001    ,A        ,S42
002    ,A B C    ,S51 S51 S52
003    ,A B      ,S33 S32

I want to split the field "line" and "code" into rows to be conform
with a real data base.  This must be done automaticaly.  So it need to
look like this after the modification:

ID     ,Line     ,Code
001    ,A        ,S42
002    ,A        ,S51
002    ,B        ,S51
002    ,C        ,S52
003    ,A        ,S33
003    ,B        ,S32

Basicaly, I need to split the field into rows and not into other
fields!!!

Can anyone help me with this?

Alex
[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
 
Hi Alex,

I believe you are just missing comma(s)
after Dispatch fields:

-----------SQL----------------------
SELECT conv3.apptID,
left([LC],1) AS Line,
left([DISP],4) AS Dispatch,
left([EST],3) AS Estimate
FROM conv3

UNION ALL

SELECT conv3.apptID,
Mid([LC],5,1),
Mid([DISP],8,4),
Mid([EST],7,3)
FROM conv3


ORDER BY apptID, Line, Dispatch, estimate;

Alexandre Saillant said:
Thank you,

Since I am not really good in Programmation and SQL, I was unable to
reproduce what you explained, I had a few bugs trying to do so.
However, you SQL statement inspired me and I came up with somekind of
a thing that seem to work:
---------SQL-----------------
SELECT conv3.apptID,
left([LC],1) AS Line,
left([DISP],4) AS Dispatch
FROM conv3

UNION ALL

SELECT conv3.apptID,
Mid([LC],5,1),
Mid([DISP],8,4)
FROM conv3

ORDER BY apptID, Line, Dispatch;
---------SQL---------------------

Note: there is four space between the caraters and YES there is more
than 3 possible lines. Can be up to 10 or so! All I have to do is to
reproduce the Mid([LC])...) things for up to 10, and second query to
clear the null fields and it's done!

But I still have a problem!!! Because I was trying to save time in my
first email, I did not include a third Field called [EST] and when I
try to put this in the SQL statement, it does not work!!!!!!!

This is what I cam up with....
-----------SQL----------------------
SELECT conv3.apptID,
left([LC],1) AS Line,
left([DISP],4) AS Dispatch
left([EST],3) AS Estimate
FROM conv3

UNION ALL

SELECT conv3.apptID,
Mid([LC],5,1),
Mid([DISP],8,4)
Mid([EST],7,3)
FROM conv3


ORDER BY apptID, Line, Dispatch, estimate;
-----------------SQL---------------------------

Why do I get someking of TYPO error message with this?????










"Gary Walter" <[email protected]> wrote in message
Hi Alex,

Pardon me for butting in, but the
following function (adapted from
a function Dirk provided on this ng)
should be a big help (if you are using
Access 200x) as Tom mentioned.

Save the following function in a module.

'*** start code ****
Public Function fNthElement(KeyString As Variant, _
Delimiter As String, _
ByVal ElementNo As Integer) As Variant
Dim arrSegments As Variant

arrSegments = Split(KeyString, Delimiter, -1, vbTextCompare)
If ((ElementNo - 1) <= UBound(arrSegments)) _
And (ElementNo > 0) Then
fNthElement = arrSegments(ElementNo - 1)
Else
fNthElement = Null
End If

End Function
'*** end code ***

Then (if you will only have max of
three elements in your fields), the following
query should do the job.

SELECT tblCode.ID,
fNthElement([Line]," ",1) AS SplitLine,
fNthElement(
Code: 
," ",1) AS SplitCode
FROM tblCode
UNION ALL
SELECT tblCode.ID,
fNthElement([Line]," ",2),
fNthElement([Code]," ",2)
FROM tblCode
WHERE
fNthElement([Line]," ",2) IS NOT NULL
AND
fNthElement([Code]," ",2) IS NOT NULL
UNION ALL
SELECT tblCode.ID,
fNthElement([Line]," ",3),
fNthElement([Code]," ",3)
FROM tblCode
WHERE
fNthElement([Line]," ",3) IS NOT NULL
AND
fNthElement([Code]," ",3) IS NOT NULL
ORDER BY ID, SplitLine, SplitCode;

Ah...the power of SQL!

Good luck and apologies again for butting in,

Gary Walter


[QUOTE="Tom Ellison"]
Dear Alex:

The general approach I'd suggest would be a UNION of a set of queries.

Write a query that returns the first piece:

001 ,A ,S42
002 ,A ,S51
003 ,A ,S33

Then another query that returns the second piece, omitting rows that
don't have a second piece:

002 ,B ,S51
003 ,B ,S32

Finally, the same for the third (and last?) piece:

002 ,C ,S52

If there could be more than 3 pieces, you need to know how many are
possible now, and perhaps in the future if it will run for data as yet
unknown.

You can use the INSTR function and SUBSTR (for Jet databases) to split
up the strings by finding spaces (I think that's what separate the
data, right?)  An alternative is to write a function that finds the
Nth piece out of the string.  If you have more than 3 possible pieces,
I'd recommend this strongly.  It will be messy in the query for more
than 3 pieces (prediction).

You can create this UNION and append to your table from that, or
append each piece.  The result will be the same except that UNION
eliminates duplicates.  There may be no duplicates, or you may not
want to ignore them.  That's a piece of research for your decision.

What's up with the leading commas?  Would you maybe be better off in
the long run without them?

Not a bad project, I'd think.  Its a good thing the way you put it:
"a real database."  The change you propose is a pretty good idea, for
sure, assuming you understand the meaning of the mess you're being
given.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On 16 Dec 2003 14:26:04 -0800, [email protected] (Alexandre
Saillant) wrote:

Hello,

I am about to acheive a dream project but one obstacle is preventing
me from ultimate success.  I really need professional help here!

I have a table that look like this:

ID     ,Line     ,Code
001    ,A        ,S42
002    ,A B C    ,S51 S51 S52
003    ,A B      ,S33 S32

I want to split the field "line" and "code" into rows to be conform
with a real data base.  This must be done automaticaly.  So it need to
look like this after the modification:

ID     ,Line     ,Code
001    ,A        ,S42
002    ,A        ,S51
002    ,B        ,S51
002    ,C        ,S52
003    ,A        ,S33
003    ,B        ,S32

Basicaly, I need to split the field into rows and not into other
fields!!!

Can anyone help me with this?

Alex
[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
 
Back
Top