[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/89d3664c/attachment-0002.html>