Is there TRANSPOSE function in ACESS database

P

Prasad

Hi,
I am trying to arrange data from linear columns to rows and TRANSPOSE
function is avilable in ACESS or not, any help is highly appreciated
 
P

Prasad

Hi BeWyched,

Thank you for reply, but the problem is data is like 147000 rows and 1356
columns, now u know excel cant support that many rows and columns. So far i
am breaking down in to bits and pieces and doing it in excel, but then data
quality may get screwed up, if i do too much manipulations manually, trying
to find out a way programatically.

Thanks
Prasad
 
J

Jason Lepack

Check out Method 2...

Hi BeWyched,

Thank you for reply, but the problem is data is like 147000 rows and 1356
columns, now u know excel cant support that many rows and columns. So far i
am breaking down in to bits and pieces and doing it in excel, but then data
quality may get screwed up, if i do too much manipulations manually, trying
to find out a way programatically.

Thanks
Prasad





- Show quoted text -
Click to expand...
 
B

BeWyched

Hi Jason

Isn't the problem here the number of rows/columns. I believe that the
maximum number of columns (i.e.fields) in an Access database is 255 so such
databases can't be used. Do you know if there is a maximum number of fields
in a VBA generated recordset?
A possibility might be to use arrays, allthough a 147,000 by 1,356 array
throws up an 'Out of Memory' error (but a 14,700 by 1,356) doesn't). So,
perhaps, a chain of 10 or so arrays might be the solution.

BW
 
J

Jason Lepack

No that's not a maximum in Access. The maximum in Excel is 255
columns and 65536 rows (in excel 2003, I hear the row size has
changed). The only maximum in Access is the 2GB file size maximum,
and whatever hardware restrictions you have.

Now, if you ahve determined that your solution may not be optimal then
I would question why you want to perform such a function? I know that
I wouldn't want to try to read a report with 147000 columns.
 
B

BeWyched

Sorry Jason, but Access also has a limit of 256 columns (fields), the same as
Excel. I know that there is also a limit on the number of rows but can't
remember what it is other than being huge!

BW
 
J

John Nurick

Hi Prasad,

You'll have understood by now that Access generally has a limit of 256
fields in a table or recordset, which means that even importing your
1,356 columns would be difficult, and the transposed table with its
147,000 columns would be quite impossible. So whatever your task is (I
can't imagine any situation in which a table of 147,000 columns is
desirable), Access probably isn't the right tool for it.

If this data is in some other database, does that have a way of
transposing the data (like Access's Crosstab query)? If not, I'd export
the data to a text file and write code to transpose that.

There's a VB/VBA TransposeDelimitedFile() procedure on my website at
http://www.j.nurick.dial.pipex.com/Code/ , but it relies on reading the
whole file into memory. Your 1,356 columns * 147,000 rows means almost
200 million data points, so this may not be possible unless you have
lots of physical and virtual memory. But there are ways of transposing a
file that is too large to fit in memory.
 
Top