Access和/或SQL中的交叉表查询以完成表组合

Question

我有一个调查(多标准决策分析)，它将结果转储到下表，名为CriteriaWeighting。

User | Criteria1 | Criteria2 | ... | CriterX
--------------------------------------------
Name1|   n1w1    |    n1w2   | ... |   n1wX |
Name2|   n2w1    |    n2w2   | ... |   n2wX |
...

我想创建一个名为WeightinStats的新表，其结构如下：

Criteria  |  Average  |  StDev  |  Var  |
-----------------------------------------
Criteria1 |  AVG(c1)  | StD(c1) | Var(c1)
Criteria2 |  AVG(c2)  | StD(c2) | Var(c2)
Criteria3 |  AVG(c3)  | StD(c3) | Var(c3)
...       |  ...      | ...     | ...
CriteriaX |  AVG(cX)  | StD(cX) | Var(cX)

其中AVG(c1)、StD(c1)、Var(c1)等是上述所有标准加权响应(n1w1、n1we、n1wX等)的平均值、std和方差。

我尝试过实现交叉表查询，但它从来没有正确的出现，所以我想知道是否有一种方法可以通过SQL强制创建所需的表。我正在使用MSAccess 2010。

谢谢你的帮助。

Answer 1

不幸的是，交叉表是相反的。如果您从所需的结构(WeightsinStats)开始，并且需要原始结构(CriteriaWeighting)，那么交叉表查询就可以实现它。当然，在转换后的终端上进行聚合。

原因是，交叉表查询以这样的方式转换记录集:从一个列中的值项变成单独的单独列，并聚合相应的数字: Sum()、Avg()、Count().

但是，您可能需要执行一个UNION查询。请注意:根据复杂性，对联合的数量有访问SQL限制。

SELECT 'Criteria1'  As Criteria, Avg([Criteria1]), StDev([Criteria1]), Var([Criteria1])
FROM CriteriaWeighting
UNION 
SELECT 'Criteria2' As Criteria, Avg([Criteria2]), StDev([Criteria2]), Var([Criteria2])
FROM CriteriaWeighting
...

如果有多个条件，则可以使用VBA动态构建联合查询：

Dim db As Database
Dim strSQL As String
Dim qdef As QueryDef
Dim i As Integer, AllCriteriaCount As Integer

AllCriteriaCount = ??  'ENTIRE FULL COUNT OF CRITERIA HERE

Set db = Currentdb

' BUILD SQL STRING
For i = 1 To AllCriteriaCount

  If i = 1 Then
    strSQL = "SELECT 'Criteria1'  As Criteria, Avg([Criteria1]), StDev([Criteria1]), Var([Criteria1])" & _
             " FROM CriteriaWeighting"
  Else
    strSQL = strSQL & "UNION" & _
             " SELECT 'Criteria" & i & "' As Criteria, Avg([Criteria" & i & "]), StDev([Criteria" & i & "]), Var([Criteria" & i & "])" & _
             " FROM CriteriaWeighting"
  End If
Next i

' CREATE QUERY DEF (STORED QUERY)
Set qdef = db.CreateQueryDef("WeightinStatsquery", strSQL)

' MAKE-TABLE TO OUTPUT FINAL
db.Execute "SELECT * WeightinStatsquery INTO WeightinStats"

Set qdef = Nothing
Set db = Nothing