I am trying to convert columns of data that was imported from an Excel 

spreadsheet into an access table.  I need to loop through the recordset 

and for each instance of the #1 in the first field begin a new record.



Example: Current Data



Field1  Field2        

   1    Carmel High

   2    Greyhounds

   3    blue/gold

   1    North Central

   2    Braves

   3    red/white 



Desired Result:

School         Nickname    Colors

Carmel High    Greyhounds  blue/gold

North Central  Braves      red/white

If you can add a column that identifies each row of data as belonging to a

unique school, you can use an access crosstab query to get the results you

want.  That is, if you were to add a column like so:



Field1	Field2		Field3

1		Carmel High		1

2		Greyhounds		1

3		blue/gold		1

1		North Central	2

2		Braves		2

3		red/white		2



You'd get what you want from the following query:



TRANSFORM First(Sheet1.Field2) AS FirstOfField2

SELECT Sheet1.Field3

FROM Sheet1

GROUP BY Sheet1.Field3

PIVOT Sheet1.Field1;



If adding that third column isn't feasible, I imagine you'd have to go to

VBA--define a recordset for the data you have, another for the data you want

& go row-by-row copying data from one to the other.



Hope that's helpful...



-Roy



-----Original Message-----

From: LaDonna Swoope-Tucker [mailto:lswoope@n...]

Sent: Thursday, February 22, 2001 6:48 PM

To: Access

Subject: [access] 2nd Try:Convert columns of data into rows of records





I am trying to convert columns of data that was imported from an Excel 

spreadsheet into an access table.  I need to loop through the recordset 

and for each instance of the #1 in the first field begin a new record.



Example: Current Data



Field1  Field2        

   1    Carmel High

   2    Greyhounds

   3    blue/gold

   1    North Central

   2    Braves

   3    red/white 



Desired Result:

School         Nickname    Colors

Carmel High    Greyhounds  blue/gold

North Central  Braves      red/white