How can I transpose a table/query in Access?

jenniferhelen

I am working with a query that has 6 columns and 101 rows; I would like to transpose the rows and columns. I have tried using a crosstab query but Access limits the row "fields" to 3 and this was not producing the desired output. I saw a comment in a thread about using a pseudo row field as a work around but I do not understand how this works.
Another option I tried is from the following link and attempting method 2: http://support.microsoft.com/kb/202176. I must say I am not a programmer and only have a vague idea of what is going on in the code. However I gave it a shot and entered my query information into the Immediate window. When I selected enter I received an immediate error "3027 record is too large", however a table was created with 101 columns correctly but only 3 of the 6 rows were created and none of the numeric data was populated relating to the rows and columns. At this point I am lost and frustrated and would greatly appreciate any advice you can offer.
Jennifer

NeoPa

Having followed the link myself, it seems to provide a decent solution for your problem. I'm not sure what you want from us.

I know the concept is not the most straightforward, but if you can't understand it I fail to see how we can help. If there's a specific point you get stuck on ask away, but we can't do your work for you.

We wouldn't even if we did know enough about your particular problem, but we don't anyway, of course.

NeoPa

I've just found your other posted question, which I've replied to. If Excel is an option for you then it is much simpler. Excel does it all for you. Such a facility is not available in Access, even if something can be designed to do the work for you.

ADezii

Quote:

Originally Posted by jenniferhelen

I am working with a query that has 6 columns and 101 rows; I would like to transpose the rows and columns. I have tried using a crosstab query but Access limits the row "fields" to 3 and this was not producing the desired output. I saw a comment in a thread about using a pseudo row field as a work around but I do not understand how this works.
Another option I tried is from the following link and attempting method 2: http://support.microsoft.com/kb/202176. I must say I am not a programmer and only have a vague idea of what is going on in the code. However I gave it a shot and entered my query information into the Immediate window. When I selected enter I received an immediate error "3027 record is too large", however a table was created with 101 columns correctly but only 3 of the 6 rows were created and none of the numeric data was populated relating to the rows and columns. At this point I am lost and frustrated and would greatly appreciate any advice you can offer.
Jennifer

I agree with NeoPa in that the posted Link is indeed a decent solution. I am revising the code to eliminate the Target Argument. The New Target Name will be the Source Name plus _Transposed. If the Target already exists, it will be Deleted. You will only need to supply a Source Name. If you do not wish to manually supply a Source Name, that can even be eliminated but one step at a time. I will also include all this functionality in a Demo Database for you, so you can get a better picture of everything. Be patient - will see you soon.

NeoPa

ADezii,

Bear in mind there is a parallel thread (How do you successfully transpose rows into columns in Excel?) posted by Jennifer which looks at an alternative approach that doesn't need this to be done within Access as such.

There's no reason you shouldn't go ahead if it's something that grabs you, but just so as you know ;)

ADezii

Quote:

Originally Posted by NeoPa

ADezii,

Bear in mind there is a parallel thread (How do you successfully transpose rows into columns in Excel?) posted by Jennifer which looks at an alternative approach that doesn't need this to be done within Access as such.

There's no reason you shouldn't go ahead if it's something that grabs you, but just so as you know ;)

Thanks NeoPa, and I am aware of what you have stated. I've developed an Algorithm which should theoretically eliminate all Column restrictions as previously indicated by the OP. This Algorithm does not create a Table or Fields, and is not hampered by Access Table limitations. Access does the Transposing internally, and then outputs the Data to a Comma Delimited File in the same Directory as the Database and named Source & _Transposed.txt. This File can now be easily Imported into a variety of External Applications. It has not been refined at this point but I'll post it anyway in case anyone is interested. Guess I'm just bored! (LOL)!

Expand|Select|Wrap|Line Numbers

 Function fTranspose(strSource As String)

Dim MyDB As DAO.Database

Dim rstSource As DAO.Recordset

Dim i As Integer

Dim strBuild As String

On Error GoTo fTranspose_Err
 
Set MyDB = CurrentDb()

Set rstSource = MyDB.OpenRecordset(strSource)

rstSource.MoveLast: rstSource.MoveFirst
 
Open CurrentProject.Path & "\" & strSource & "_Transposed.txt" For Output As #1
 
'The Transposing is done within this Nested Structure

With rstSource

  For i = 0 To .Fields.Count - 1

    Do While Not .EOF

      strBuild = strBuild & .Fields(i).Value & ", "

      .MoveNext

    Loop

      .MoveFirst

       Debug.Print .Fields(i).Name & ", " & Left$(strBuild, Len(strBuild) - 2)

         Print #1, .Fields(i).Name & ", " & Left$(strBuild, Len(strBuild) - 2)

           strBuild = ""       'reset

  Next i

End With
 
Close #1
 
rstSource.Close

Set rstSource = Nothing
 
Exit_fTranspose:

  Exit Function
 
fTranspose_Err:

  Select Case Err

    Case 3078

      MsgBox "The Object " & strSource & " doesn't exist."

    Case Else

      MsgBox Err.Description, vbExclamation, "Error in fTranspose()"

  End Select

    Resume Exit_fTranspose:

End Function

NeoPa

Quote:

Originally Posted by ADezii

Guess I'm just bored! (LOL)!

I expect Jennifer is thanking her lucky stars at the moment that she caught you at an idle moment :D

jenniferhelen

ADezii,
I am extremely grateful for all the time you spent on my problem. I have spent much time trying to use it but I am very inexperienced in this area. Whenever I try to run the code, I am prompted with a macro box. I do not have any macros in this database so I am not sure what is expected at this point. I entered an "a" at this prompt and then selected the Create button (the only other option was the Cancel button). The result was the following text was added after your code, "sub a() end sub".
Again I really appreciate all you have done.

NeoPa,
I appreciate your comments as well and I never meant to imply I wanted anything done for me. I apologize if I offended you.

Regards,
Jennifer

ADezii

Jennifer, download the Demo (Attachment) that I have now made available to you. With it you can Transpose any 1 of 3 Tables to a TAB Delimited/Quotes surrounding Strings, Text File. This File can then be easily Imported into Excel.

jenniferhelen

ADezii,
Thanks again for the code and example database. It works perfectly and beautifully in the example. I am in the process of implementing it in my database. I really appreciate all your help.
Take care,
Jennifer

ADezii

Quote:

Originally Posted by jenniferhelen

ADezii,
Thanks again for the code and example database. It works perfectly and beautifully in the example. I am in the process of implementing it in my database. I really appreciate all your help.
Take care,
Jennifer

Not a problem, Jennifer.

NeoPa

Quote:

Originally Posted by jenniferhelen

NeoPa,
I appreciate your comments as well and I never meant to imply I wanted anything done for me. I apologize if I offended you.

You're good Jennifer.

I do appreciate how difficult it is to express a question clearly enough to garner the right sort of response.

I'm happy you found ADezii for this anyway.