在MS Access中先汇总数据再关联多表的实现方法

1. 为每张表创建金额汇总查询 #

先对A、B、C、D四张表分别按Division、Vendor、Deal、Reference分组，汇总对应金额，生成各表的汇总查询：

• 表A的汇总查询（命名为qry_A_Sum）：

SELECT Division, Vendor, Deal, Reference, Sum(Amount) AS [Amount A]
FROM A
GROUP BY Division, Vendor, Deal, Reference;

• 同理创建表B、C、D的汇总查询，分别命名为qry_B_Sum、qry_C_Sum、qry_D_Sum，将金额列对应命名为[Amount B]、[Amount C]、[Amount D]。

2. 获取所有唯一的分组键集合 #

通过UNION合并四张汇总查询的分组键，自动去重得到所有可能的Division+Vendor+Deal+Reference组合，创建查询qry_All_Keys：

SELECT Division, Vendor, Deal, Reference FROM qry_A_Sum
UNION
SELECT Division, Vendor, Deal, Reference FROM qry_B_Sum
UNION
SELECT Division, Vendor, Deal, Reference FROM qry_C_Sum
UNION
SELECT Division, Vendor, Deal, Reference FROM qry_D_Sum;

3. 关联所有汇总表生成最终结果 #

以qry_All_Keys为基础，分别左连接四张汇总查询，确保保留所有分组键，同时匹配各表的汇总金额。若某键在对应表中无匹配，可通过Nz函数将Null转换为0（按需选择），创建最终查询qry_Final_Result：

SELECT 
    k.Division,
    k.Vendor,
    k.Deal,
    k.Reference,
    Nz(a.[Amount A], 0) AS [Amount A],
    Nz(b.[Amount B], 0) AS [Amount B],
    Nz(c.[Amount C], 0) AS [Amount C],
    Nz(d.[Amount D], 0) AS [Amount D]
FROM 
    ((qry_All_Keys k
LEFT JOIN qry_A_Sum a ON 
    k.Division = a.Division 
    AND k.Vendor = a.Vendor 
    AND k.Deal = a.Deal 
    AND k.Reference = a.Reference)
LEFT JOIN qry_B_Sum b ON 
    k.Division = b.Division 
    AND k.Vendor = b.Vendor 
    AND k.Deal = b.Deal 
    AND k.Reference = b.Reference)
LEFT JOIN qry_C_Sum c ON 
    k.Division = c.Division 
    AND k.Vendor = c.Vendor 
    AND k.Deal = c.Deal 
    AND k.Reference = c.Reference)
LEFT JOIN qry_D_Sum d ON 
    k.Division = d.Division 
    AND k.Vendor = d.Vendor 
    AND k.Deal = d.Deal 
    AND k.Reference = d.Reference;

关键说明 #

• 先汇总再关联：原表的分组字段组合不唯一，提前汇总可避免关联后出现重复行，保证每个键组合仅对应一条记录。
• 用UNION去重：确保分组键集合无重复，避免后续关联产生冗余数据。
• 替代全连接：MS Access不支持直接的FULL JOIN，通过“收集所有唯一键+左连接”的方式，实现保留所有表数据的需求。

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