<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2722.900" name=GENERATOR>
<STYLE>@font-face {
font-family: Tahoma;
}
@page Section1 {size: 8.5in 11.0in; margin: 1.0in 1.25in 1.0in 1.25in; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: purple; TEXT-DECORATION: underline
}
P.MsoAutoSig {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoAutoSig {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoAutoSig {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
SPAN.emailstyle17 {
FONT-WEIGHT: normal; COLOR: windowtext; FONT-STYLE: normal; FONT-FAMILY: Tahoma; TEXT-DECORATION: none
}
SPAN.emailstyle19 {
FONT-WEIGHT: normal; COLOR: blue; FONT-STYLE: normal; FONT-FAMILY: Tahoma; TEXT-DECORATION: none
}
SPAN.EmailStyle20 {
FONT-WEIGHT: normal; COLOR: blue; FONT-STYLE: normal; FONT-FAMILY: Tahoma; TEXT-DECORATION: none
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=purple link=blue>
<DIV><SPAN class=686234023-05022003><FONT face=Arial color=#0000ff size=2>It's
not too difficult to write, just a pain. The trick is that when you split
it, you need to keep track of what you pulled. Since you aren't using 97,
you don't need to worry about it.</FONT></SPAN></DIV>
<DIV><SPAN class=686234023-05022003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=686234023-05022003><FONT face=Arial color=#0000ff
size=2>Drew</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Mark Boyd
[mailto:MarkBoyd@mcbeeassociates.com]<BR><B>Sent:</B> Wednesday, February 05,
2003 3:26 PM<BR><B>To:</B> accessd@databaseadvisors.com<BR><B>Subject:</B> RE:
[AccessD] Reformatting a table's data<BR><BR></FONT></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face=Tahoma color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">Drew
-</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">Thanks for the
code.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">I was pulling my
hair out trying to create my own 'split' function.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">Mark</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">-----Original
Message-----<BR><B><SPAN style="FONT-WEIGHT: bold">From:</SPAN></B> Drew Wutka
[mailto:DWUTKA@marlow.com] <BR><B><SPAN
style="FONT-WEIGHT: bold">Sent:</SPAN></B> Wednesday, February 05, 2003 2:35
PM<BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B>
'accessd@databaseadvisors.com'<BR><B><SPAN
style="FONT-WEIGHT: bold">Subject:</SPAN></B> RE: [AccessD] Reformatting a
table's data</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Use this
code:</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Dim rs
As ADODB.Recordset<BR>Dim rs2 As ADODB.Recordset<BR>Dim ExceptionArray() As
String<BR>Dim i As Long<BR>Set rs = New ADODB.Recordset<BR>Set rs2 = New
ADODB.Recordset<BR>rs.Open "tblOriginal", CurrentProject.Connection,
adOpenKeyset, adLockReadOnly, adCmdTableDirect<BR>rs2.Open "tblFinal",
CurrentProject.Connection, adOpenKeyset, adLockOptimistic,
adCmdTableDirect<BR>If rs.EOF = False Then rs.MoveFirst<BR>Do Until rs.EOF =
True<BR> ExceptionArray = Split(rs.Fields(1).Value,
",")<BR> For i = 0 To
UBound(ExceptionArray)<BR>
rs2.AddNew<BR> rs2.Fields(0).Value =
rs.Fields(0).Value<BR>
rs2.Fields(1).Value =
Val(ExceptionArray(i))<BR>
rs2.Update<BR> Next i<BR>
rs.MoveNext<BR>Loop<BR>rs.Close<BR>Set rs = Nothing<BR>rs2.Close<BR>Set rs2 =
Nothing</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Of
course change the table names. I can send you a sample db with this code
in it. Boy I'm glad you are using A2k (and that's a rare thing, cause I
hate A2k....) because I didn't feel like writing out a split
function....<VBG></SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Drew</SPAN></FONT></P></DIV>
<BLOCKQUOTE style="MARGIN-TOP: 5pt; MARGIN-BOTTOM: 5pt; MARGIN-RIGHT: 0in">
<P class=MsoNormal
style="MARGIN-BOTTOM: 12pt; MARGIN-LEFT: 0.5in; MARGIN-RIGHT: 0in"><FONT
face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">-----Original
Message-----<BR><B><SPAN style="FONT-WEIGHT: bold">From:</SPAN></B> Mark
Boyd [mailto:MarkBoyd@mcbeeassociates.com]<BR><B><SPAN
style="FONT-WEIGHT: bold">Sent:</SPAN></B> Wednesday, February 05, 2003 1:18
PM<BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B>
accessd@databaseadvisors.com<BR><B><SPAN
style="FONT-WEIGHT: bold">Subject:</SPAN></B> RE: [AccessD] Reformatting a
table's data</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Tahoma color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">Drew
-</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Tahoma color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">I'm
using Access2000.</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Tahoma color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">I
looked up the Split function, but am a bit confused.</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Tahoma color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">Do
you have an example of this?</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Tahoma color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">Thanks,</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Tahoma color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">Mark</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">-----Original
Message-----<BR><B><SPAN style="FONT-WEIGHT: bold">From:</SPAN></B> Drew
Wutka [mailto:DWUTKA@marlow.com] <BR><B><SPAN
style="FONT-WEIGHT: bold">Sent:</SPAN></B> Wednesday, February 05, 2003 2:08
PM<BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B>
'accessd@databaseadvisors.com'<BR><B><SPAN
style="FONT-WEIGHT: bold">Subject:</SPAN></B> RE: [AccessD] Reformatting a
table's data</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Arial color=blue
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Sounds
like the split function is what you want. What version of Access are
you using. 97 doesn't have the split function.</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Arial color=blue
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Drew</SPAN></FONT></P></DIV>
<BLOCKQUOTE
style="MARGIN-TOP: 5pt; MARGIN-BOTTOM: 5pt; MARGIN-RIGHT: 0in"><P
class=MsoNormal
style="MARGIN-BOTTOM: 12pt; MARGIN-LEFT: 1in; MARGIN-RIGHT: 0in"><FONT
face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">-----Original
Message-----<BR><B><SPAN style="FONT-WEIGHT: bold">From:</SPAN></B> Mark
Boyd [mailto:MarkBoyd@mcbeeassociates.com]<BR><B><SPAN
style="FONT-WEIGHT: bold">Sent:</SPAN></B> Wednesday, February 05, 2003
12:53 PM<BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B>
AccessD@databaseadvisors.com<BR><B><SPAN
style="FONT-WEIGHT: bold">Subject:</SPAN></B> [AccessD] Reformatting a
table's data</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">I have the following 3
records:</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">ClaimNo
ExceptionCodes</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">1
1,2,3</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">2
9,10</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">3
1,11,20</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">What I am looking to do is
reformat the table, so the records are easier to work
with.</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">I would like my result to
look like this:</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">ClaimNo
ExceptionCode</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">1
1</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">1
2</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">1
3</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">2
9</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">2
10</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">3
1</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">3
11</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">3
20</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">Any ideas?</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">TIA,</SPAN></FONT></P>
<P class=MsoAutoSig style="MARGIN-LEFT: 1in"><FONT face=Tahoma
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">Mark
Boyd</SPAN></FONT></P>
<P class=MsoAutoSig style="MARGIN-LEFT: 1in"><FONT face=Tahoma
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">Sr. Systems
Analyst</SPAN></FONT></P>
<P class=MsoAutoSig style="MARGIN-LEFT: 1in"><FONT face=Tahoma
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">McBee
Associates, Inc.</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face="Times New Roman"
size=3><SPAN
style="FONT-SIZE: 12pt"></SPAN></FONT> </P></BLOCKQUOTE></BLOCKQUOTE></DIV></BLOCKQUOTE></BODY></HTML>