moving rows to coulmns

[Andrew]

Ok, I have tables with 2 - 3 columns, and from 40 - 600
rows, imported from a text file, created by ArcCatalog.
I need to turn the rows into columns, and vice versa.
There is nothing to total so a crosstab query is out. Do
I need to export all these tables into Excel, copy, paste
special, transpose, and then back into Access? Is there
another way?

Thanks. Andrew

 

[Lynn Trapp]

Andrew,
You must first realize that, even if you could do this, you are limited to
255 columns in an Access database. Thus a table with over 255 columns could
not be imported that way.

Can you please describe the data a little more and tell us why you want to
do this?

 

[Duane Hookom]

In addition to Lynn's comments, you don't have to have columns to total in
order to create a crosstab.

I would definitely question "why" you would want to do this.

 

[Guest]

Thanks for the input. I have spatial data from over 400
spatial files in ArcCatalog that needs to be placed into
a database. When exporting from ArcCatalog, the spatial
data field names are placed in rows, ie.
horizontal Datum Name: NA Datum of 1983
Ellipsoid Name: Geodetic Reference System 80
etc, etc, etc
What I am trying to do is get horizontal datum name,
ellipsoid name, etc., etc. to be column headings with the
NA Datum of 1983, Geodetic Reference System 80, etc. etc.
to be the info in the rows under the column heading. Why
does this need to be done? It is because I am a flunky
and have been instructed to put all our spatial data into
MSAccess tables. The modelers and programmers need to
know what info we have collected so they can incorpoarate
this info into their models. In addition, this data will
eventually be exported from MSAccess into Oracle. The
255 column limit is not a problem as I only need to have
about 15 columns of info. It is just that as the data
stands now, coming from ArcCatalog, every single piece of
info is exported, resulting in over 300 rows of data that
I wanted to put into column headings.
What I am doing now is creating a query that I hope will
do what I want. Thanks for the help.

Andrew

 

[Duane Hookom]

When you import the data, select the colon as the delimiter so your data
will create records with two columns/fields. You can then create a crosstab
with one field as the Column Heading, and the other as the Value (select
First). Create a new column in the grid and use something like:
MadeUp:"A record"
This should display your data as un-normalized and across rather than
normalized and down. If you can't figure this out, come back with about 10
records and how you. would like them to display

 

[Guest]

Thanks! That works fine. Now, if I only knew how to
write a query or macro that prompts me for a table name,
I could run the macro and just sit here putting in each
of my 400 or so table names. I can write macros but do
not know how to add a prompt for an object name. Again,
thanks for the help. Your solution works good.

Andrew