Запрос перекрестной таблицы в Access и/или SQL для выполнения комбинации таблиц

У меня есть опрос (анализ решений по нескольким критериям), результаты которого выводятся в следующую таблицу с именем 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 и т. д.)

Я попытался реализовать перекрестный запрос, но он никогда не выходит правильно, поэтому мне интересно, есть ли способ принудительно создать нужную таблицу с помощью SQL. Я использую MSAccess 2010.

Спасибо за любую помощь.


ms-access-2010 ms-access crosstab
person Extinct23    schedule 10.04.2015    source источник

Ответы (1)


arrow_upward
2
arrow_downward

К сожалению, перекрестная таблица идет другим путем. Если вы начали с желаемой структуры (WeightsinStats) и нуждались в исходной структуре (CriteriaWeighting), этого можно добиться с помощью перекрестных запросов. Разумеется, с агрегацией на преобразованном конце.

Причина в том, что кросс-табличные запросы преобразует набор записей таким образом, что элементы значений из одного столбца становятся отдельными отдельными столбцами с агрегированием соответствующих числовых значений: Sum(), Avg( ), Считать()...

Однако, возвращаясь назад, вам может понадобиться запрос UNION. Обратите внимание: в Access 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
person Parfait    schedule 10.04.2015
comment
Большой. Спасибо за вашу помощь. Возможно, мне просто придется переработать некоторые элементы файла db. - person Extinct23; 15.04.2015