Vba
將 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”的新表中。