Multiple Values Displaying one Field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a data dump that was given to me. Some of the fields have multiple 3 digit values that are being separated by a space. I could have 2 values or up to 10 values in these fields. See below for example:

RECORD # CODE
1 180 185
2 1A0 1A3 104 1A4 109

How do I separate these values into multiple records?
Thanks
 
Do you need to set up a routine to do this on a regular basis or is this a
one-time event. If it is one time, you might want to open in Word and
replace the spaces with tabs then convert to a table. Then, copy and paste
into Excel and move columns around.

--
Duane Hookom
MS Access MVP


DG said:
I have a data dump that was given to me. Some of the fields have multiple
Click to expand...
3 digit values that are being separated by a space. I could have 2 values
or up to 10 values in these fields. See below for example:
 
DG said:
I have a data dump that was given to me. Some of the fields have multiple 3 digit
Click to expand...
values that are being separated by a space. I could have 2 values or up to 10 values
in these fields. See below for example:
RECORD # CODE
1 180 185
2 1A0 1A3 104 1A4 109

How do I separate these values into multiple records?
Click to expand...

Hi DG,

Here be an Access 200x solution
that makes sense to me (provided by
Tom earlier this week).

Save the following function (adapted
from a function provided by Dirk)
to a module.

If you need help with this, please ask.

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

If Len(Trim(KeyString & "")) > 0 Then
arrSegments = Split(KeyString, Delimiter, -1, vbTextCompare)
If ((ElementNo - 1) <= UBound(arrSegments)) _
And (ElementNo > 0) Then
fNthElement = arrSegments(ElementNo - 1)
Else
fNthElement = Null
End If
Else
fNthElement = Null
End If
End Function
'*** end of function ***

Next copy the following Union query
to WordPad. Do a "search and replace"
for "tblCode" replacing with the name of
your table.When done, copy to clipboard.

Start a new query in Query Designer,
switch to SQL view, and paste your
query from the clipboard. Save your query.



SELECT tblCode.[RECORD#],
fNthElement(
Code: 
," ",1) AS SplitCode
FROM tblCode
WHERE
fNthElement([CODE]," ",1) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",2)
FROM tblCode
WHERE
fNthElement([CODE]," ",2) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",3)
FROM tblCode
WHERE
fNthElement([CODE]," ",3) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",4)
FROM tblCode
WHERE
fNthElement([CODE]," ",4) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",5)
FROM tblCode
WHERE
fNthElement([CODE]," ",5) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",6)
FROM tblCode
WHERE
fNthElement([CODE]," ",6) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",7)
FROM tblCode
WHERE
fNthElement([CODE]," ",7) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",8)
FROM tblCode
WHERE
fNthElement([CODE]," ",8) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",9)
FROM tblCode
WHERE
fNthElement([CODE]," ",9) IS NOT NULL
UNION ALL SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",10)
FROM tblCode
WHERE
fNthElement([CODE]," ",10) IS NOT NULL
ORDER BY [RECORD#], SplitCode;

Please respond back if you have any problems.

Good luck,

Gary Walter
 
Hi DG,

If there is a chance you might have
more than a single space between
the values, save the following function
(kindly provided by Ken on this ng) to
your module, then run an update query
to "removeextraspaces" before you run
the Union query. For example:

UPDATE tblCode
SET tblCode.CODE = RemoveExtraSpaces(
Code: 
);


'*** start function ****
Function RemoveExtraSpaces(strIn As String) As String
Dim strCurrChar As String
Dim strNewString As String
Dim intSpaceCount As Integer
Dim intLoop As Integer
Dim intLenString As Integer
' "2402    NW 26   St" becomes "2402 NW 26 ST"
' Removes all but one space

intLenString = Len(strIn)

For intLoop = 1 To intLenString
strCurrChar = Mid(strIn, intLoop, 1)
If strCurrChar = " " Then
intSpaceCount = intSpaceCount + 1
Else
intSpaceCount = 0
End If
If intSpaceCount < 2 Then strNewString = strNewString & strCurrChar
Next intLoop
RemoveExtraSpaces = Trim(strNewString)

End Function
'*** end function ***

Note: This function will expect your field to not
contain any NULL's. If that is not possible,
change your Update query to filter out NULL
[CODE]'s.

UPDATE tblCode
SET tblCode.CODE = RemoveExtraSpaces([CODE])
WHERE [CODE] IS NOT NULL;

Please respond back if something is not clear.

Good luck,

Gary Walter
 
Seasons Greetings,

You can split the Code string into multiple rows very
easily with the RAC (Relational Application Companion)
utility.RAC runs on any version of Sql Server 2000.
At the very least you can install the MSDE version of
server (which is easy to get).You can then export your
Access data to server,import Access data to server,
use an ADP,link to your Access table(s) from the server etc.

RAC can do many things from complex crosstabs to
analyzing hierachies.And it can usually do them without
any sql coding.It's intended to solve problems quicky
and easily.Given any task there are many options to
choose from.So you have flexibility along with easy of use.
You will find that RAC is in many ways similar to the Access
crosstab query.But it can do much more than crosstabs:).
Advanced sql users will find that RAC is tightly integrated
with the server so you can use server sql within RAC to
do just about anything (see the @select parameter in Help).

For example, here is a simple RAC solution for your sample data.
To make things easy we'll create the data on the server.

create table #DG ([RECORD #] int primary key,CODE varchar(100))
go
insert #DG values(1,'180 185')
insert #DG values(2,'1A0 1A3 104 1A4 109')

--select * from #DG

Here we split the CODE string based on the sequence (position)
of each delimited string from left to right.RAC can be driven thru
its own GUI or executed in batch just like any server stored procedure.
Here is the batch execute.

Exec Rac
-- Specify the method of splitting.
@split='[position]',
-- Specify what columns should repeat for each split string part.
@rows='[RECORD #] & [position] ',
-- Specify the column to be split
@pvtcol='CODE',
@from='#DG', -- Just like a server FROM statement.
-- RAC also has a @where statement
-- just like the server WHERE.
-- We give the string parts the column name 'col1'.
-- You could give any name,ie @rank='str' would
-- name the column 'str1'.
-- RAC has its own SELECT option with many
-- special features.You could use it to rename
-- 'col1' or do just about anything else you wanted
-- using any valid server syntax.
@rank='col',
-- Delimiter used in CODE, ie. a blank (' ').
@separator=' ',
@rowbreak='n', -- Similar to breaking on columns in Access Reports
-- Here we don't want duplicate [RECORD #]'s
-- to be suppressed,so we display each one
('n'='no').
@defaults1='y',
-- Eliminate position from the result.
@defaultexceptions='[position]'

Result:

RECORD # col1
-------- ----
1 180
1 185
2 1A0
2 1A3
2 104
2 1A4
2 109

As stated above, with any task you have many options
to choose from.Here we split CODE based on the string
part values (ie. order them by value).We also create
counters that may be used in subsequent processing
or just for display.

Exec Rac
@split='[value]',
@rows='[RECORD #] & [value] ',
@pvtcol='CODE',
@from='#DG',
@rank='col',
@separator=' ',@rowbreak='n',@defaults1='y',
-- Define a counter of CODES within each [RECORD #]
-- We choose to give the counter column the name 'CODEcnter'
@rowcounters='[RECORD #]{CODEcnter}',
-- Define a counter over the entire result.This is the internal
-- RAC record counter.It is always column 'rd'.
@tablecounter='y',
@defaultexceptions='[value]'

Result:

rd RECORD # CODEcnter col1
----------- -------- --------- ----
1 1 1 180
2 1 2 185
3 2 1 104
4 2 2 109
5 2 3 1A0
6 2 4 1A3
7 2 5 1A4

Note that *no* sql coding was necessary for any of this.

Of course there are many other options.You could
save the result to a server table,create complex
expressions easily etc.

For all the details on RAC and QALite (a free adminstration
and querying tool for MSDE) visit:
www.rac4sql.net

Happy Holidays to All from
RacTeam
 
Gary Walter said:
.
.
Next copy the following Union query
to WordPad. Do a "search and replace"
for "tblCode" replacing with the name of
your table.When done, copy to clipboard.

Start a new query in Query Designer,
switch to SQL view, and paste your
query from the clipboard. Save your query.
Click to expand...
SELECT tblCode.[RECORD#],
fNthElement(
Code: 
," ",1) AS SplitCode
FROM tblCode
WHERE
fNthElement([CODE]," ",1) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",2)
FROM tblCode
WHERE
fNthElement([CODE]," ",2) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",3)
FROM tblCode
WHERE
fNthElement([CODE]," ",3) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",4)
FROM tblCode
WHERE
fNthElement([CODE]," ",4) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",5)
FROM tblCode
WHERE
fNthElement([CODE]," ",5) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",6)
FROM tblCode
WHERE
fNthElement([CODE]," ",6) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",7)
FROM tblCode
WHERE
fNthElement([CODE]," ",7) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",8)
FROM tblCode
WHERE
fNthElement([CODE]," ",8) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",9)
FROM tblCode
WHERE
fNthElement([CODE]," ",9) IS NOT NULL
UNION ALL SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",10)
FROM tblCode
WHERE
fNthElement([CODE]," ",10) IS NOT NULL
ORDER BY [RECORD#], SplitCode;

Please respond back if you have any problems.[/QUOTE]

What would you think if a given CODE had a 1000 parts,2000 parts?

Just kidding around,hope your a good sport :~)

Happy Holidays,
RTF
Click to expand...
 
Gary Walter said:
Hi DG,

If there is a chance you might have
more than a single space between
the values, save the following function
(kindly provided by Ken on this ng) to
your module, then run an update query
to "removeextraspaces" before you run
the Union query. For example:

UPDATE tblCode
SET tblCode.CODE = RemoveExtraSpaces(
Code: 
);
[snip]
Please respond back if something is not clear.[/QUOTE]

Hi Gary,

What if there were multiple delimiters?Have you worked
with data coming from a mainframe?

Just more teasing.I'm sure you guys would come up
with something.
Should I post an example of this kind of data or would you kill me? :~)

Best,
RTF
Click to expand...
 
Obviously....I'd switch to MSDE
and use RAC 8-)

Rufus T. Firefly said:
Gary Walter said:
.
.
Next copy the following Union query
to WordPad. Do a "search and replace"
for "tblCode" replacing with the name of
your table.When done, copy to clipboard.

Start a new query in Query Designer,
switch to SQL view, and paste your
query from the clipboard. Save your query.
Click to expand...
SELECT tblCode.[RECORD#],
fNthElement(
Code: 
," ",1) AS SplitCode
FROM tblCode
WHERE
fNthElement([CODE]," ",1) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",2)
FROM tblCode
WHERE
fNthElement([CODE]," ",2) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",3)
FROM tblCode
WHERE
fNthElement([CODE]," ",3) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",4)
FROM tblCode
WHERE
fNthElement([CODE]," ",4) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",5)
FROM tblCode
WHERE
fNthElement([CODE]," ",5) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",6)
FROM tblCode
WHERE
fNthElement([CODE]," ",6) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",7)
FROM tblCode
WHERE
fNthElement([CODE]," ",7) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",8)
FROM tblCode
WHERE
fNthElement([CODE]," ",8) IS NOT NULL
UNION ALL
SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",9)
FROM tblCode
WHERE
fNthElement([CODE]," ",9) IS NOT NULL
UNION ALL SELECT tblCode.[RECORD#],
fNthElement([CODE]," ",10)
FROM tblCode
WHERE
fNthElement([CODE]," ",10) IS NOT NULL
ORDER BY [RECORD#], SplitCode;

Please respond back if you have any problems.[/QUOTE]

What would you think if a given CODE had a 1000 parts,2000 parts?

Just kidding around,hope your a good sport :~)

Happy Holidays,
RTF
[/QUOTE]
Click to expand...
Click to expand...
 
Gary Walter said:
Obviously....I'd switch to MSDE
and use RAC 8-)
Click to expand...

Yep, you are a good sport:~).

We figure (or hope:) more Access users will
begin to investigate MSDE as they can get for it for
$0.While the leaning curve to becoming a really
good server jock can be steep,RAC/MSDE
shouldn't be very hard.And isn't Jets days
numbered anyway?I think MS is trying hard to
push people off Jet.So we're trying to make
the jump less painful:).
A port to any Access version would take forever
so that's out of the question.Interesting a port
to MySql next year is a possibility and MySql
is free:).

We're all ears if you have any comments/questions/
flames/corrections:~)

Best,
RTF
www.rac4sql.net
 
Back
Top