將 MS Access 2007 表中的數據重新排列為新的表格式/結果輸出

我有具有以下格式的訪問表：

--------------------------------------
|Name |Math|Science|Reading|Civics|
--------------------------------------
|Mike | A  |  C    |  A    | B    |
|John | B  |  A    |  A    | B    |
|Randy| A  |  C    |  A    | A    |
|Sasha| B  |  C    |  D    | A    |
|Max  | A  |  C    |  A    | A    |
|Jenn | A  |  A    |  A    | C    |
-----------------------------------

是否有任何 Access Sql 程式碼或 Access VBA 腳本可供我使用，以便以以下格式轉換（或在查詢/結果集中輸出）上表？

--------------------
|   Name      |Grade|
---------------------
|Mike Math    | A   |
|Mike Science | c   | 
|Mike Reading | A   |
|Mike Civics  | B   |
|John Math    | B   |
|John Science | A   | 
|John Reading | A   |
|John Civics  | B   |
|Randy Math   | A   |
etc... 

到目前為止，我發現以下 2 個執行緒顯示瞭如何在 Access 中使用 VBA 將所有欄位名稱引入數組或只是基本輸出。這是我想要完成的工作的開始（因為本質上我需要將欄位名稱引入第一列）。如果我能弄清楚，我會回帖。

https://stackoverflow.com/questions/11548697/how-to-list-fields-name-in-table-in-access-using-sql

http://forums.asp.net/t/1143922.aspx

我想知道我是否可以做類似的事情……

For each fieldname in table (where fieldname != Name)
Begin 
select 
Name + ' ' + Subject as Name, 
(select Grade from table where Name = Name and Subject = Subject) as Grade
End

是的，可以使用UNION ALL：

SELECT [Name] & " Math" AS col1, tab1.Math FROM tab1
UNION ALL
SELECT [Name] & " Science" AS col1, tab1.Science FROM tab1
UNION ALL
SELECT [Name] & " Reading" AS col1, tab1.Reading FROM tab1
UNION ALL
SELECT [Name] & " Civics" AS col1, tab1.Civics FROM tab1
Order BY 1;

在表單中創建一個按鈕並粘貼以下程式碼

Private Sub Command0_Click()
Dim db As DAO.Database
Dim rst As New ADODB.Recordset

Dim tbl As TableDef
Set db = CurrentDb
For Each tbl In db.TableDefs
   If tbl.Name = "Result" Then
   db.Execute "DROP TABLE Result"
   Else
   End If
Next tbl

db.Execute "CREATE TABLE Result(Names varchar(100),Grade varchar(10))"

Set db = CurrentDb

rst.Open "Tab1", CurrentProject.Connection, adOpenDynamic

Dim n As Integer
Dim fieldname As String
Dim str As String

For n = 1 To rst.Fields.Count - 1
   fieldname = rst.Fields(n).Name
   str = "Insert Into Result ([Names],Grade) SELECT [Name] & " & Chr(34) & " " & fieldname & Chr(34) & " As Names," & "tab1." & fieldname & " As Grade FROM tab1;"

   db.Execute str


Next n

End Sub

無論有多少可用欄位，此程式碼都可以正常工作。我得到了 cha 的 UNION ALL Query 的幫助並開發了程式碼。結果將儲存在一個名為“Result”的新表中。

引用自：https://dba.stackexchange.com/questions/57585