Transpose Table in Current Database

[Sally757]

{NeoPa - Admin} This question was split off from another thread (How can I transpose a table/query in Access?).

ADezii

I used your download and it worked quite well for me also but I only need the code to transpose the table and save it under the same name with transposed in the same database? Do I need to do this as a form or can I go ahead and select the macro directly? How would I make the changes required?

[ADezii]

Sorry Sally, didn't realize that you had already Posted in the Access/VBA Forum. Currently on Vacation but will return Thursday morning where I would be more than happy to revisit this question again. This is assuming, of course, that no one provides an answer before then.

[ADezii]

I'm still on vacation without Access, but I figured that I would Post a few comments as well as an outline indicating how you can go about this process.

1. The first point that you should be made aware of is that you have a 255 Fields/Columns restriction within an Access Table. This means that if you have greater than 255 Rows in you Table, the Data cannot be Transposed. You can, however, as in the Demo, created a Comma-Delimited Text File then Import it into Excel if > 255.
2. That being said, here is my General Outline as to how I would Transpose the Data into another Table in the DB.
   1. We will assume your Table in named tblData. If the number of Rows in tblData is > 255, then the Transport simply cannot be done, and we will exit the Code. If the number of Rows is <= 255 then the Code will fall thru.
   2. Pass the number of Rows in tblData (lngNumOfRows), along with the Table Name in a Variable (strTblName) to a Public Function.
   3. This Function will dynamically create a Table named strTableName with lngNumOfRows Fields/Columns. For the sake of simplicity all Fields will be Type Text. If we had to analyze each Field in tblData then match the corresponding Field Type in tblData_Transpo sed, things would be complicated real fast. You can easily change the Types after the Table has been created then populated.
   4. A Recordset based on tblData is then created and a 2-Dimensional Array is then populated with the 1st Dimension representing the Row and the 2nd Dimension representing the Column. The Intersection of a Row and Column MyArray(Row, Column) in the Array will contain the Data for the Row/Column Intersection.
   5. We will create another Recordset based on the New Table (strTblName_Tran sposed) that will be used specifically to populate the newly created Table.
   6. We will now iterate thru MyArray using Nested For...Next Loops and basically swap the Row and Column Dimensions, populating strTblName_Tran sposed in the process.
3. I know that this all sounds confusing, but I wanted to give you some indication as to ho this can be done. Work on it in the meantime and see what you can come up with until I return on Thursday morning.

[ADezii]

This was a little more difficult than I thought, or it could be my first day back from Vacation! (LOL). In any event, I was able to Transpose Data in a Table following the Guidelines that I indicated in Post# 15. Rather than go into a detailed explanation of how this all works, I'll simply Attach the Demo that I have created for you, and should there be any questions, please feel free to ask.

P.S. One item that I forgot to incorporate into the Code was the checking of the 255 Field Limit in an Access Table. This point is very important but can easily be implemented within the existing Code. Good Luck with your Project.

[Sally757]

Good afternoon,

First, thank you for your time and expertise, they are appreciated. Your example was perfect.

I have a few general questions regarding the transpose code.

Is there any way the transpose could of been done using queries? Is there any other way besides a form to initiate the transpose code? Such as by running a query?

I want the database to import the data, add the new data to a table with the existing table, transpose the data and then report on the transposed data, is this the best method to do all this?

Again thank you.

[NeoPa]

It is now possible again to respond to this thread. Please report any hijacks when you notice them.

[ADezii]

Is there any way the transpose could of been done using queries?

The Transpose Code will work with any Object(s) from which a Recordset can be generated, namely: a Table, Query, or a SQL Statement.

Is there any other way besides a form to initiate the transpose code? Such as by running a query?

Any method that a Function Procedure can be called will work such as: an assignment expression, Function Call from an AutoExec Macro, etc. It could possibly be called within a Query via a Calculated Field, but this would not make much sense.

I want the database to import the data, add the new data to a table with the existing table, transpose the data and then report on the transposed data, is this the best method to do all this?

What I see as ideal would be to Transpose the Data while in its original state, then perform the Append and Report operations. Let's see if there are any other thoughts on your questions.

[Sally757]

Thank you again.

I would like the column headings to become the row headings in the transposed table. What is the best way to proceed with this? If you want an example I can send you one.

[ADezii]

If you want an example I can send you one.

That is always a good idea, if only for clarification.

[NeoPa]

Sally:
Is there any way the transpose could of been done using queries?

I imagine the question was more about using an alternative approach to the code than using a query as the input to the code ADezii. Something like a CrossTab query.

Sally:
Is there any other way besides a form to initiate the transpose code? Such as by running a query?

Bear in mind that a function may be called from a query but then it tends to be called for every line of the query - assuming it has a parameter passed that's a field from the query.