如何在Access中基于多值可变列查询创建紧凑交叉表

我在MS-Access数据库中通过连接三个表生成了如下查询数据：

simsID  Forename    Surname Class   AssessmentName  Mark    Percentage
1234    Joe         Bloggs  13X     Test1           20      50
1235    Fred        Bloggs  13Y     Test1           31      77.5
1234    Joe         Bloggs  13X     Test2           30      60
1235    Fred        Bloggs  13Y     Test2           10      20
1235    Fred        Bloggs  13Y     Test3           20      33.3333333333333
1234    Joe         Bloggs  13X     Test3           34      56.6666666666667

期望将数据展示为如下紧凑格式：

ID  Forename    Surname Class   Test1 Mark  Test1 % Test2 Mark  Test2 % Test3 Mark  Test3 %
1234    Joe         Bloggs  13X     20      50      30      60      34      56.6666666666667
1235    Fred        Bloggs  13Y     31      77.5    10      20      20      33.3333333333333

当前做法是创建两个交叉表查询再内连接：

AllStudentData_Marks 查询：

TRANSFORM Avg(AllStudentData.Mark) AS AvgOfMark
SELECT AllStudentData.simsID AS ID, AllStudentData.Forename AS Forename, AllStudentData.Surname AS Surname, AllStudentData.Class AS Class
FROM AllStudentData
GROUP BY AllStudentData.simsID, AllStudentData.Forename, AllStudentData.Surname, AllStudentData.Class
PIVOT AllStudentData.Assessments.AssessmentName & " Mark";

AllStudentData_Percentage 查询：

TRANSFORM Avg(AllStudentData.Percentage) AS AvgOfPercentage
SELECT AllStudentData.simsID AS ID, AllStudentData.Forename AS Forename, AllStudentData.Surname AS Surname, AllStudentData.Class AS Class
FROM AllStudentData
GROUP BY AllStudentData.simsID, AllStudentData.Forename, AllStudentData.Surname, AllStudentData.Class
PIVOT AllStudentData.Assessments.AssessmentName & " %";

连接查询：

SELECT AllStudentData_Marks.*, AllStudentData_Percentage.*
FROM AllStudentData_Marks INNER JOIN AllStudentData_Percentage ON AllStudentData_Marks.ID = AllStudentData_Percentage.ID;

但结果会重复ID、姓名、班级等列，且列名带有查询前缀。由于评估列数量不固定，无法硬编码列名，请问如何修改才能得到无重复列、列名合理的紧凑目标表？

方法1：通过UNION ALL合并指标后做单交叉表（无需VBA） #

这种方法无需拆分两个交叉表，先把Mark和Percentage转成统一的指标结构，再一次性做交叉表，自动生成所需的列：

1. 创建中间查询 CombinedMetricData：

SELECT simsID, Forename, Surname, Class, AssessmentName & " Mark" AS Metric, Mark AS Value
FROM AllStudentData
UNION ALL
SELECT simsID, Forename, Surname, Class, AssessmentName & " %" AS Metric, Percentage AS Value
FROM AllStudentData

这个查询会把每个学生的Mark和Percentage分别作为一条记录，标记对应的指标名称（比如Test1 Mark、Test1 %）。

2. 基于中间查询创建交叉表 CombinedStudentAssessments：

TRANSFORM Avg(Value) AS AvgOfValue
SELECT simsID AS ID, Forename, Surname, Class
FROM CombinedMetricData
GROUP BY simsID, Forename, Surname, Class
PIVOT Metric;

执行这个交叉表后，会自动按评估名称生成对应的TestX Mark和TestX %列，且主列（ID、姓名、班级）只出现一次，完全符合你的目标格式。

方法2：用VBA动态生成合并查询（适用于需保留原交叉表的场景） #

如果需要保留原有的两个交叉表查询，可通过VBA自动获取所有评估名称，动态拼接无重复列的SQL语句：

Sub CreateCombinedQuery()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim combinedSql As String
    Dim colSegments As String
    
    Set db = CurrentDb()
    
    ' 获取所有唯一的评估名称
    Set rs = db.OpenRecordset("SELECT DISTINCT AssessmentName FROM AllStudentData ORDER BY AssessmentName")
    
    ' 拼接每个评估对应的Mark和%列
    colSegments = ""
    Do While Not rs.EOF
        colSegments = colSegments & ", AllStudentData_Marks.[" & rs!AssessmentName & " Mark], AllStudentData_Percentage.[" & rs!AssessmentName & " %]"
        rs.MoveNext
    Loop
    rs.Close
    
    ' 构建最终SQL，只保留一份主列
    combinedSql = "SELECT AllStudentData_Marks.ID, AllStudentData_Marks.Forename, AllStudentData_Marks.Surname, AllStudentData_Marks.Class" & _
                  colSegments & _
                  " FROM AllStudentData_Marks INNER JOIN AllStudentData_Percentage ON AllStudentData_Marks.ID = AllStudentData_Percentage.ID"
    
    ' 保存为新查询（如果已存在则先删除）
    On Error Resume Next
    db.QueryDefs.Delete "CombinedStudentAssessments"
    On Error GoTo 0
    db.CreateQueryDef "CombinedStudentAssessments", combinedSql
    
    MsgBox "合并查询已创建完成"
    
    Set rs = Nothing
    Set db = Nothing
End Sub

运行这段VBA代码后，会自动生成一个没有重复主列、列名符合要求的合并查询，且能自动适配新增的评估名称。

内容的提问来源于stack exchange，提问作者MartynW