使用Access中的共享字段连接两个查询

Question

我有两个问题: QueryA和QueryB。两者都产生了相似的结果。QueryA:月份，ID，countA和QueryB: month，ID，countB。

每个报告的计数值都是唯一的，但是月份和ID将在两者之间共享。示例：

QueryA：

Month | ID | Count

 Jan | 1 | 57

 Feb | 1 | 63

 Jan | 2 | 44

 Feb | 2 | 11

QueryB：

Month | ID | Count 

 Jan | 1 | 22 

 Feb | 1 | 3 

 Jan | 2 | 99 

 Feb | 3 | 21

我需要一个产生以下结果的最后查询: QueryC：

Month | ID | CountA | CountB

 Jan | 1 | 57 | 22

 Feb | 1 | 63 | 3

 Jan | 2 | 44 | 99

 Feb | 2 | 11 |

 Feb | 3 |      | 21

请原谅任何缺乏格式。我正在用我的手机发邮件。

我需要一个最终的输出，它(1)为Month+ID提供了一个统一的结果，这个结果同时存在于查询中，也存在于查询中.如果在QueryA中而不是B中，如果在QueryB中而不是A中，则在一个结果中(没有重复)。

Answer 1

备选案文1，交叉表：

创建这样的联合查询：

SELECT ID
    ,Mnth AS [Month]
    ,Cnt
    ,"CountA" AS hdr
FROM QueryA

UNION

SELECT ID
    ,Mnth AS [Month]
    ,Cnt
    ,"CountB"
FROM QueryB

将其保存在名称下，例如qryAll1。创建交叉表查询：

TRANSFORM Sum(qryAll1.Cnt) AS SumOfCnt
SELECT qryAll1.ID
    ,qryAll1.Month
FROM qryAll1
GROUP BY qryAll1.ID
    ,qryAll1.Month
PIVOT qryAll1.hdr;

同样，使用一个查询：

TRANSFORM Sum(u.Cnt) AS SumOfCnt
SELECT u.ID
    ,u.Month
FROM (
    SELECT ID
        ,Mnth AS [Month]
        ,Cnt
        ,"CountA" AS hdr
    FROM QueryA
    UNION
    SELECT ID
        ,Mnth AS [Month]
        ,Cnt
        ,"CountB"
    FROM QueryB
    ) AS u
GROUP BY u.ID
    ,u.Month
PIVOT u.hdr;

变式2，按

创建联合查询

SELECT ID, Mnth as [Month], Cnt as CountA, 0 as CountB
FROM QueryA
UNION SELECT ID, Mnth as [Month], 0, Cnt
FROM QueryB;

将其保存为qryAll2并创建查询

SELECT qryAll2.ID
    ,qryAll2.Month
    ,Sum(qryAll2.CountA) AS SumOfCountA
    ,Sum(qryAll2.CountB) AS SumOfCountB
FROM qryAll2
GROUP BY qryAll2.ID
    ,qryAll2.Month

同样，使用一个查询：

SELECT u.ID
    ,u.Month
    ,Sum(u.CountA) AS SumOfCountA
    ,Sum(u.CountB) AS SumOfCountB
FROM (
    SELECT ID
        ,Mnth AS [Month]
        ,Cnt AS CountA
        ,0 AS CountB
    FROM QueryA
    UNION
    SELECT ID
        ,Mnth AS [Month]
        ,0
        ,Cnt
    FROM QueryB
    ) AS u
GROUP BY u.ID
    ,u.Month
ORDER BY u.ID
    ,u.Month DESC;