MS Access Transposing

[jdlewin1]

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

[ADezii]

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.

[jdlewin1]

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.

[Lilit1]

Hi everyone..
I am a new member here , ))

[ADezii]

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!

[jdlewin1]

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!