当前数据库中的转置表 [英] Transpose Table in Current Database

问题描述

{NeoPa - Admin}这个问题是从另一个线程中分离出来的（（27.0 KB，68 views）

{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?

解决方案

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.

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_Transposed, 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_Transposed) 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_Transposed 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.

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.

Attached Files

Transpose With Array.zip (27.0 KB, 68 views)

这篇关于当前数据库中的转置表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！