Turning An Access Query Data from horizontal to vertical

Can you be a little more specific about what you mean by the vertical format.  Normal columns are across the top, and rows down the left let's say.  If you want the columns down the left, then what do you want across the top, certainly not a column for each row, that would get too wide quickly.  Or does the query just return a small amount of data?  

Either way a bit more detail on your need would help get a solution that works.

---

»bp

Sounds like you need a "crosstab" query. There's a wizard that can possibly help with that - click the "Query Wizard" button, and select the "Crosstab Query Wizard".

the query is quite large containing about a hundred fields - I basically need to transpose the the data so that instead of looking like this:
acct#      acctname      benefit     effDate             etc etc ect
100         toms bar         m435     05/23/2017

it would look like this:
acct#             100
acct name     toms bar
benefit           m435
effdate           05/23/2017
etc
etc
etc

Where do you want the data from the second record to show?

---

»bp

it would be just one single acct - so it would be just the info on that one particular acct

What is the purpose of doing this?  Are you trying to create an export file?  There is no query that will do this so it needs to be VBA code but we'd need some idea of where the data is going.

I will be exporting it to an excel file to cols A and B where column A will be the headers and column B will be the values

see this link first
http://support.microsoft.com/kb/202176

here are other similar threads, just tweak to suit your need

https://www.experts-exchange.com/questions/26209275/How-to-transpose-a-access-table-when-exporting-to-excel.html?anchorAnswerId=32834777#a32834777

https://www.experts-exchange.com/questions/22988724/Is-This-Possible-in-Access-Convert-column-type-data-to-row-type.html?anchorAnswerId=20368499#a20368499

https://www.experts-exchange.com/questions/23563476/Transpose-column-data-into-row-data.html?anchorAnswerId=22001066#a22001066

all I can say is wow rey - I have been trying to do this for years with no success. I used the code microsoft gave and it went perfect - I will be using this code a lot - I cant thank you enought!!!!!

just awesum code thank you

Can you share the query you ended up with for the benefit of future visitors to this question?

---

»bp

Heres the code:

Private Sub Command22_Click()

Dim db As DAO.Database
Dim tdfNewDef As DAO.TableDef
Dim fldNewField As DAO.Field
Dim rstSource As DAO.Recordset, rstTarget As DAO.Recordset
Dim i As Integer, j As Integer
Dim fd, rn As String

Dim oExcel As Excel.Application
Dim oWorkBook As Excel.Workbook
Dim oWorksheet As Excel.Worksheet
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

DoCmd.SetWarnings False
DoCmd.Hourglass True

Set db = CurrentDb

fd = Format(Now, "yyyymmddhhnnss")
rn = [Forms]![Reports_Form]![Report_Acct_Name]

'this does a make table grabbing a specific record using the above acct name    
DoCmd.OpenQuery "Report Rate Sheet Audit"
 
Set db = CurrentDb()
Set rstSource = db.OpenRecordset("Report_Rate_Sheet_Audit")
rstSource.MoveLast

' Create a new table to hold the transposed data.
' Create a field for each record in the original table.
' I use this for loop to del the audit2 table so it can be recreated each time
For Each OneTable In CurrentDb.TableDefs
        If OneTable.Name = "Report_Rate_Sheet_Audit2" Then _
        DoCmd.DeleteObject ObjectType:=acTable, ObjectName:=OneTable.Name
Next OneTable

Set tdfNewDef = db.CreateTableDef("Report_Rate_Sheet_Audit2")
For i = 0 To rstSource.RecordCount
   Set fldNewField = tdfNewDef.CreateField(CStr(i + 1), dbText)
   tdfNewDef.Fields.Append fldNewField
Next i
db.TableDefs.Append tdfNewDef

' Open the new table and fill the first field with
' field names from the original table.
Set rstTarget = db.OpenRecordset("Report_Rate_Sheet_Audit2")
For i = 0 To rstSource.Fields.Count - 1
   With rstTarget
     .AddNew
     .Fields(0) = rstSource.Fields(i).Name
     .Update
   End With
Next i

rstSource.MoveFirst
rstTarget.MoveFirst
' Fill each column of the new table
' with a record from the original table.
For j = 0 To rstSource.Fields.Count - 1
   ' Begin with the second field, because the first field
   ' already contains the field names.
   For i = 1 To rstTarget.Fields.Count - 1
      With rstTarget
         .Edit
         .Fields(i) = rstSource.Fields(j)
         rstSource.MoveNext
         .Update
      End With

   Next i
   rstSource.MoveFirst
   rstTarget.MoveNext
Next j

'this is to copy a template to put the exported data into
sTemplateA = CurrentProject.Path & "\Reports\Rate Sheet Audit Checklist Template.xlsx"
sOutputa = CurrentProject.Path & "\FileOut\Rate Sheet Audit Checklist_" & fd & ".xlsx"
If Dir(sOutputa) <> "" Then Kill sOutputa
FileCopy sTemplateA, sOutputa

Set oExcel = New Excel.Application
Set oWorkBook = oExcel.Workbooks.Open(sOutputa)
Set oWorksheet = oWorkBook.Worksheets("Compare with new rate sheet")
Set qdf = db.QueryDefs("Report Rate Sheet Export")
Set rs = qdf.OpenRecordset(dbOpenDynaset)
oWorksheet.Range("A2").CopyFromRecordset rs

rs.Close
oWorkBook.Save
oWorkBook.Close
oExcel.Quit
db.Close

MsgBox " Table Transposed and Exported"

DoCmd.SetWarnings True
DoCmd.Hourglass False

End Sub

Great Help Thanks again