#  Other Applications & Softwares  > Access Tables & Databases >  >  How to transpose rows and columns in access

## Isaacliu

Dear Expert

i would like to do the transpose from a query to a table.

The query is shown as pic1 and table is shown as pic2.

i am the beginner, no much knowledge in access, please help.

Thank you very much.

b1.jpgb2.jpg

----------


## alansidman

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.

----------


## Isaacliu

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

----------


## alansidman

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

----------


## Isaacliu

Thz, but i havent learn VBA, please help to change

----------


## alansidman

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.

----------


## Isaacliu

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.

----------


## alansidman

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.

----------


## Isaacliu

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.

----------


## alansidman

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*
Copy the Excel VBA codeSelect the workbook in which you want to store the Excel VBA codePress _Alt+F11_ to open the Visual Basic EditorChoose _Insert > Module__Edit > Paste_ the macro into the module that appearedClose the VBEditorSave your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
*To run the Excel VBA code:*Press _Alt-F8_ to open the macro listSelect a macro in the listClick the Run button

----------

