By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,584 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Transpose Table in Current Database

P: 4
{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?
Jul 23 '17 #1

✓ answered by ADezii

@Sally757:
I modified the Code in the Attachment I uploaded in Post# 4. I guess you can describe the Logic as a 'Pseudo Transpose' with the first Field in the Results Table being the Field Names and the Record Data residing horizontally as opposed to a vertical orientation. This is purely a Code-based and it involves any Object from which a DAO Recordset can be created, namely a: Table, Query, or SQL Statement. Simply change the Value of strSource in the Click() Event of the Command Button and the Code will do the rest. Please be aware of the number of Columns Restriction in Access which means that the number of Records in the Data Source cannot exceed 255, actually 254 with the Row Headings in place. I'm not really sure if this is what you are looking for, only you can be the judge of that.

Share this Question
Share on Google+
13 Replies


ADezii
Expert 5K+
P: 8,599
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.
Jul 24 '17 #2

ADezii
Expert 5K+
P: 8,599
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.
Jul 25 '17 #3

ADezii
Expert 5K+
P: 8,599
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
File Type: zip Transpose With Array.zip (27.0 KB, 60 views)
Jul 27 '17 #4

P: 4
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.
Jul 29 '17 #5

NeoPa
Expert Mod 15k+
P: 31,186
It is now possible again to respond to this thread. Please report any hijacks when you notice them.
Aug 7 '17 #6

ADezii
Expert 5K+
P: 8,599
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.
Aug 8 '17 #7

P: 4
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.
Sep 15 '17 #8

ADezii
Expert 5K+
P: 8,599
If you want an example I can send you one.
That is always a good idea, if only for clarification.
Sep 15 '17 #9

NeoPa
Expert Mod 15k+
P: 31,186
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.
Sep 15 '17 #10

ADezii
Expert 5K+
P: 8,599
@Sally757:
I modified the Code in the Attachment I uploaded in Post# 4. I guess you can describe the Logic as a 'Pseudo Transpose' with the first Field in the Results Table being the Field Names and the Record Data residing horizontally as opposed to a vertical orientation. This is purely a Code-based and it involves any Object from which a DAO Recordset can be created, namely a: Table, Query, or SQL Statement. Simply change the Value of strSource in the Click() Event of the Command Button and the Code will do the rest. Please be aware of the number of Columns Restriction in Access which means that the number of Records in the Data Source cannot exceed 255, actually 254 with the Row Headings in place. I'm not really sure if this is what you are looking for, only you can be the judge of that.
Attached Files
File Type: zip Transpose With Row Headings.zip (27.9 KB, 59 views)
Sep 15 '17 #11

P: 4
Hi,

Sorry for the delay. This is exactly what I was looking for. Again thank you.
Sep 27 '17 #12

P: 1
Can you please Send me zip files on my email id: {Email address removed from public forum -NeoPa}.

Transpose with Row Headings.zip
Transpose with Array.zip

Because I am unable to download the files
Oct 18 '17 #13

ADezii
Expert 5K+
P: 8,599
As soon as I get a chance, will do so. For the future, it would probably have been a better idea to send me a Private Message (PM) with your EMail Address.

*Sent @ 12:00 P.M., check E-Mail.
Oct 18 '17 #14

Post your reply

Sign in to post your reply or Sign up for a free account.