[AccessD] Reformatting a table's data

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.
 
Drew

-----Original Message-----
From: Mark Boyd [mailto:MarkBoyd at mcbeeassociates.com]
Sent: Wednesday, February 05, 2003 3:26 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Reformatting a table's data



Drew -

Thanks for the code.

I was pulling my hair out trying to create my own 'split' function.

 

Mark

 

-----Original Message-----
From: Drew Wutka [mailto:DWUTKA at marlow.com] 
Sent: Wednesday, February 05, 2003 2:35 PM
To: 'accessd at databaseadvisors.com'
Subject: RE: [AccessD] Reformatting a table's data

 

Use this code:

 

Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim ExceptionArray() As String
Dim i As Long
Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
rs.Open "tblOriginal", CurrentProject.Connection, adOpenKeyset,
adLockReadOnly, adCmdTableDirect
rs2.Open "tblFinal", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTableDirect
If rs.EOF = False Then rs.MoveFirst
Do Until rs.EOF = True
    ExceptionArray = Split(rs.Fields(1).Value, ",")
    For i = 0 To UBound(ExceptionArray)
        rs2.AddNew
        rs2.Fields(0).Value = rs.Fields(0).Value
        rs2.Fields(1).Value = Val(ExceptionArray(i))
        rs2.Update
    Next i
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing
rs2.Close
Set rs2 = Nothing

 

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>

 

Drew

-----Original Message-----
From: Mark Boyd [mailto:MarkBoyd at mcbeeassociates.com]
Sent: Wednesday, February 05, 2003 1:18 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Reformatting a table's data

Drew -

I'm using Access2000.

I looked up the Split function, but am a bit confused.

Do you have an example of this?

 

Thanks,

Mark

 

-----Original Message-----
From: Drew Wutka [mailto:DWUTKA at marlow.com] 
Sent: Wednesday, February 05, 2003 2:08 PM
To: 'accessd at databaseadvisors.com'
Subject: RE: [AccessD] Reformatting a table's data

 

Sounds like the split function is what you want.  What version of Access are
you using.  97 doesn't have the split function.

 

Drew

-----Original Message-----
From: Mark Boyd [mailto:MarkBoyd at mcbeeassociates.com]
Sent: Wednesday, February 05, 2003 12:53 PM
To: AccessD at databaseadvisors.com
Subject: [AccessD] Reformatting a table's data

I have the following 3 records:

ClaimNo            ExceptionCodes

1                      1,2,3

2                      9,10

3                      1,11,20

 

What I am looking to do is reformat the table, so the records are easier to
work with.

I would like my result to look like this:

ClaimNo            ExceptionCode

1                      1

1                      2

1                      3

2                      9

2                      10

3                      1

3                      11

3                      20

 

Any ideas?

 

TIA,

Mark Boyd

Sr. Systems Analyst

McBee Associates, Inc.

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030205/6dcb01d5/attachment-0002.html>