[AccessD] Normalize Pivot Table

No responses but I did whip something up this morning which works.  As
I'm new to ADO as opposed to DAO, I'm not sure I'm taking the most
efficient method however given the size of my table to import it will be
well under a second.  Its basic and gets the job done for my situation.


So far this is what I have, it's a work in progress.. But in the right
direction.  Right now it takes the table (in the current database, and
normalizes the data but simply displays it in the Immediate window.  I
will add a second RS and have it add each record to a table.  In reality
it will look to an external data source, but that's easy enough to
change.

Sub NormalizeCrosstab(pivTblName As String)

Dim rs As ADODB.Recordset
Dim i As Long
Set rs = New ADODB.Recordset
rs.Open "[" & pivTblName & "]", CurrentProject.Connection, adOpenStatic,
adLockOptimistic

rs.MoveFirst
	'Go through Recordset row by row
        Do Until rs.EOF = True
		'for each row make normalized entry from each field
        	For i = 1 To rs.Fields.Count - 1 Step 1 
		'send results to immediate window 
        	Debug.Print rs.Fields(0), rs.Fields(i).Name,
rs.Fields(i)
        	Next i
        rs.MoveNext
        Loop

rs.Close
Set rs = Nothing

End Sub

In the long run I would like the the sub routine taking in parameters
such as PivotTableName, Which column to start at, ColumnName of Crosstab
header values, Columnname of Value, and the name of the table to put the
results in)  Would like to make this a utility that can be placed in any
database and use quickly simply by passing along a few parameters.  

If anyone has any criticism or ideas to add please feel free..

Thanks,
Rich


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of CYNTHIA SPELL
Sent: Thursday, March 24, 2005 7:14 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Normalize Pivot Table


I'm interested in doing something similar.  I didn't see any responses.
Did I miss something?

Cindy

>>> Rich_Lavsa at pghcorning.com 03/23/05 08:27AM >>>

Hello all,

I was just curious if anyone out there has ever written any code (ADO or
DAO) to Normalize a Pivot or Crosstab data table.  
Reason: I get a data matrix that looks like a pivot table.  The matrix
is set up with formula's in excel so its easy for the outside company to
update the entire matrix and email the updated matrix to our users.
Problem is getting the updated data into a normalized state so it can be
used in the database.  I did try UNTOOLS
(http://www.CleanDataSystems.com) which works great for a 1 time
conversion, however I need this to be an automated approach where the
user simply has to copy the file to a specified directory then push a
button in the application to update the database.
 
Yesterday I had a very clear vision of how I'd write the code but this
morning my brain isn't working too clearly.  

Any help would be greatly appreciated

Thanks,
Rich
-- 
AccessD mailing list
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com