主页 / dba / 问题 / 57585
Accepted
Juan Velez
Asked: 2014-01-27 14:56:52 +0800 CST

将 MS Access 2007 表中的数据重新排列为新的表格式/结果输出

  • 772

我有具有以下格式的 Access 表：

--------------------------------------
|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
ms-access-2007 vba

2 个回答

  1. Best Answer

    是的，可以使用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;

    结果

    • 6

  2. 在表单中创建一个按钮并粘贴以下代码

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

    • 1

相关问题