How to transpose rows and columns in access

Export the Query to Excel. Transpose the data in Excel and then import it back into Access as a new table. If you need assistance with this, then upload your exported query as an excel file to this location.

Can you do by sql?

like as

TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT Products.ProductID, Products.ProductName, Sum([Order Details].Quantity) AS Total
FROM Employees INNER JOIN (Products INNER JOIN (Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID)
ON Products.ProductID = [Order Details].ProductID)
ON Employees.EmployeeID = Orders.EmployeeID
GROUP BY Products.ProductID, Products.ProductName
PIVOT [Employees].[LastName] & ", " & [Employees].[FirstName];


i copy from website of http://allenbrowne.com/ser-67.html, but i dont know how to change, can anyone help

A SQL transform statement creates a crosstab query. See my solution in post #2 or look at this tutorial by Denis Wright

http://www.datawright.com.au/access_..._using_VBA.htm

Export your query as shown in Figure 1 to Excel. Then upload that excel sheet to this forum so that we can transpose the to the desired results and provide necessary code.

From here
Attachment 588560

The sql is very complex:
SELECT 第一場比較1V2_2.[1], 第一場比較1V2_2.[2], 第一場比較1V3_2.[3], 第一場比較1V4_2.[4], 第一場比較1V5_2.[5], 第一場比較1V6_2.[6], 第一場比較1V7_2.[7], 第一場比較1V8_2.[8], 第一場比較1V9_2.[9], 第一場比較1V10_2.[10], 第一場比較1V11_2.[11], 第一場比較1V12_2.[12], 第一場比較1V13_2.[13], 第一場比較1V14_2.[14]
FROM (((((((((((第一場比較1V2_2 LEFT JOIN 第一場比較1V3_2 ON 第一場比較1V2_2.[1] = 第一場比較1V3_2.[1]) LEFT JOIN 第一場比較1V4_2 ON 第一場比較1V2_2.[1] = 第一場比較1V4_2.[1]) LEFT JOIN 第一場比較1V5_2 ON 第一場比較1V2_2.[1] = 第一場比較1V5_2.[1]) LEFT JOIN 第一場比較1V6_2 ON 第一場比較1V2_2.[1] = 第一場比較1V6_2.[1]) LEFT JOIN 第一場比較1V7_2 ON 第一場比較1V2_2.[1] = 第一場比較1V7_2.[1]) LEFT JOIN 第一場比較1V8_2 ON 第一場比較1V2_2.[1] = 第一場比較1V8_2.[1]) LEFT JOIN 第一場比較1V9_2 ON 第一場比較1V2_2.[1] = 第一場比較1V9_2.[1]) LEFT JOIN 第一場比較1V10_2 ON 第一場比較1V2_2.[1] = 第一場比較1V10_2.[1]) LEFT JOIN 第一場比較1V11_2 ON 第一場比較1V2_2.[1] = 第一場比較1V11_2.[1]) LEFT JOIN 第一場比較1V12_2 ON 第一場比較1V2_2.[1] = 第一場比較1V12_2.[1]) LEFT JOIN 第一場比較1V13_2 ON 第一場比較1V2_2.[1] = 第一場比較1V13_2.[1]) LEFT JOIN 第一場比較1V14_2 ON 第一場比較1V2_2.[1] = 第一場比較1V14_2.[1];



to here
Attachment 588561


Thank you very much.

I don't want your SQL statement, I want to see the results in an excel spreadsheet as you have shown in your first post picture.

Additionally, I do not speak or read Chinese so your SQL statement is meaningless to me. If you really want my assistance, then please provide the information that I have requested and not something else as it is a waste of your time and mine to continue this dialogue under these circumstances.

Make sure that your excel spreadsheet is an attachment and not a picture as that will not suffice.

Please have a look, i attached pic1 and pic2 for you.

one to 14(1,2,3,4,5,6,7,8,9,10,11,12,13,14) is fixed, every time also is 1 to 14.

Here is some code to transpose your data. Once transposed, you may either import back into Access as a new table or publish as an excel spreadsheet.
I have attached the workbook with this code in it so that you may see how it works directly.

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button