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]

@jenniferhelen
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]

@NeoPa
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)!

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]

@ADezii
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]

@jenniferhelen
Not a problem, Jennifer.

[NeoPa]

@jenniferhelen
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.

[NeoPa]

Anyone interested in the hijack question inserted in here can now find it in a separate thread Transpose Table in Current Database.