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

MS Access Transposing

P: 3
Hi,

Is there a way to transpose the data from a query into an Access table. ADezii wrote some code and database example which does this but exports to a txt file.

In my instance I will have a maximum of 6 records + the original column fields (denoted in the first column of the new table as "Info".

The data is created in a query called "TOA_Report_History" (limited to the 6 records) and I want it to be transposed into an Access table called "TOA_Report_History_Transpose" which is designed with the following field columns: Info, Record1, Record2, Record3, Record4, Record5, Record6

I can then clear this table and run the code again to add new data to it as required. So the table will always be empty when the code is run.

I have attached a slimmed down version so you can see the query results format and the table format populated with the info.

Cheers
Jdlewin
Attached Files
File Type: zip MS Access Transpose.zip (357.4 KB, 29 views)
Jul 10 '17 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,599
This is a little more complicated than your typical Transpose Code. Let me put my Thinking Cap on and see what I can come up with.
Jul 10 '17 #2

P: 3
Hi ADezii.

I think your code can be used to export my data set to txt and the run some additional code as an auto upload back into the TOA_Report_History_Transpose table.

Would be better if it could do it all internal to Access tho.
Jul 11 '17 #3

P: 3
Hi everyone..
I am a new member here , ))
Jul 11 '17 #4

ADezii
Expert 5K+
P: 8,599
  1. Your particular scenario proved to be quite a challenge! (LOL).
  2. I ended up doing exactly what I did not want to do, and that is to Transpose the Data in a 2-step process.
  3. Step 1 involves Transposing the Field Names only in the Query TOA_Report_History to the Table TOA_Report_History_Transposed.
  4. Step 2 creates a Recordset based on TOA_Report_History_Transposed and Updates each Record in turn.
  5. In its current state, a MAXIMUM Number of 6 Records is allowed in TOA_Report_History. This is due to the inclusion of Fields Record1 thru Record6 only in TOA_Report_History_Transposed and your preset limit in your initial Posting. This MAXIMUM can easily be changed should you so desire.
  6. Enough chatter, I'll let you judge the results yourself and make a determination as to whether or not it is what you are looking for.
  7. I'll not go through the details of the Code, but simply Upload the Demo to you. Should you have anything further to ask, simply do so.
P.S. - Welcome aboard, Lilit1!
Attached Files
File Type: zip Transpose_Revised_2.zip (80.5 KB, 45 views)
Jul 11 '17 #5

P: 3
Ha great ADezii. I only require the 6 records as its a preset max number for the report but good to know i can extend this if needs be. I will have a look at the demo and see how it works and report back!!!

Thank you very much for your efforts!
Jul 11 '17 #6

Post your reply

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