Transpose 2 rows to 2 columns

  • Thread starter Thread starter James
  • Start date Start date
J

James

Hi, I have a query setup that outputs data to an excel file but in row
format. Id like to take every entry and transpose it to 2 columns. Id like to
make it dynamic in case we ever change what fields are in the query. Is there
a command that Access has to do this? My output table looks like this now
(lots of columns)

PD Name Qty Field1 Field2 Field 3......etc
123 test 1 abc def ghi...........etc

I want:
PD 123
Name test
Qty 1
Field1 abc
Field2 def
Field3 ghi
etc..............

Im currently using this code behind a button on a form
stDocName = "Test_Report1" 'query name
DoCmd.OutputTo acOutputQuery, stDocName, acFormatXLS, , True

thanks for your time
 
Union query --
SELECT "PD" AS Data1, PD = Data2
FROM YourTable
UNION ALL SELECT "Name" AS Data1, Name = Data2
FROM YourTable
UNION ALL SELECT "Qty" AS Data1, Qty = Data2
FROM YourTable
UNION ALL SELECT "Field1" AS Data1, Field1 = Data2
FROM YourTable
UNION ALL SELECT "Field2" AS Data1, Field2 = Data2
FROM YourTable
......
 
Back
Top