access里对同一人多条数据汇总

我现在根据示例1至示例4四个分表union出了如截图的查询，代码如下，得出的查询结果中如果人员的方案不同，那么一个人就会出现两条数据。希望达到的效果是：如“李四“这种有两条数据的人，年度限额为年度总额（代码中25000和50000，取值规则是【娱乐消费汇总】字段值为0取25000，不为0则50000）减去这两条数据金额的汇总，并且这两条数据的年度限额金额一致，如第二张截图，麻烦大手们，谢谢

img

SELECT A.姓名, A.证件号, A.方案, A.开始日期, A.结束日期, SUM(A.餐饮消费) AS 餐饮消费汇总, SUM(A.娱乐消费) AS 娱乐消费汇总, SUM(A.交通消费) AS 交通消费汇总, SUM(A.餐饮消费+A.娱乐消费+A.交通消费) AS 年度消费汇总, iif(sum(A.娱乐消费) = 0 ,25000,50000)-SUM(A.餐饮消费+A.娱乐消费+A.交通消费) AS 年度限额, SUM(B.餐饮消费) AS Q1餐饮消费, SUM(B.娱乐消费) AS Q1娱乐消费, SUM(B.交通消费) AS Q1交通消费, SUM(C.餐饮消费) AS Q2餐饮消费, SUM(C.娱乐消费) AS Q2娱乐消费, SUM(C.交通消费) AS Q2交通消费
FROM ((SELECT a.姓名, a.证件号, a.方案, a.开始日期, a.结束日期, SUM(a.餐饮消费) AS 餐饮消费, SUM(a.娱乐消费) AS 娱乐消费, SUM(a.交通消费) AS 交通消费 FROM (SELECT a1.姓名, a1.证件号, a1.方案, a1.开始日期, a1.结束日期, a1.餐饮消费, a1.娱乐消费, a1.交通消费 FROM 示例1 a1        UNION ALL        SELECT a2.姓名, a2.证件号, a2.方案, a2.开始日期, a2.结束日期, a2.餐饮消费, a2.娱乐消费, a2.交通消费 FROM 示例2 a2        UNION ALL        SELECT a3.姓名, a3.证件号, a3.方案, a3.开始日期, a3.结束日期, a3.餐饮消费, a3.娱乐消费, a3.交通消费 FROM 示例3 a3        UNION ALL        SELECT a4.姓名, a4.证件号, a4.方案, a4.开始日期, a4.结束日期, a4.餐饮消费, a4.娱乐消费, a4.交通消费 FROM 示例4 a4        )  AS a GROUP BY a.姓名, a.证件号, a.方案, a.开始日期, a.结束日期)  AS A LEFT JOIN (SELECT b.姓名, b.证件号, b.方案, b.开始日期, b.结束日期, SUM(b.餐饮消费) AS 餐饮消费, SUM(b.娱乐消费) AS 娱乐消费, SUM(b.交通消费) AS 交通消费 FROM (SELECT b1.姓名, b1.证件号, b1.方案, b1.开始日期, b1.结束日期, b1.餐饮消费, b1.娱乐消费, b1.交通消费 FROM 示例1 b1        UNION ALL        SELECT b2.姓名, b2.证件号, b2.方案, b2.开始日期, b2.结束日期, b2.餐饮消费, b2.娱乐消费, b2.交通消费 FROM 示例2 b2        )  AS b GROUP BY b.姓名, b.证件号, b.方案, b.开始日期, b.结束日期)  AS B ON A.证件号&A.方案 = B.证件号&B.方案) LEFT JOIN (SELECT c.姓名, c.证件号, c.方案, c.开始日期, c.结束日期, SUM(c.餐饮消费) AS 餐饮消费, SUM(c.娱乐消费) AS 娱乐消费, SUM(c.交通消费) AS 交通消费 FROM (SELECT c1.姓名, c1.证件号, c1.方案, c1.开始日期, c1.结束日期, c1.餐饮消费, c1.娱乐消费, c1.交通消费 FROM 示例3 c1        UNION ALL        SELECT c2.姓名, c2.证件号, c2.方案, c2.开始日期, c2.结束日期, c2.餐饮消费, c2.娱乐消费, c2.交通消费 FROM 示例4 c2        )  AS c GROUP BY c.姓名, c.证件号, c.方案, c.开始日期, c.结束日期)  AS C ON A.证件号&A.方案 = C.证件号&C.方案
GROUP BY A.姓名, A.证件号, A.方案, A.开始日期, A.结束日期;

img

-- 思路就是先根据姓名和证件号聚合后算出年度限额（看你图里的意思是根据娱乐消费汇总取25000或者50000-- -- 后，再减去年度消费汇总），算出来后，在关联一下原表，替换掉年度限额字段就好了
-- 其中b表取除了年度限额外的所有字段，a表取年度字段即可。（省略号是图里看不见的其他所有字段）

select  b.姓名, b.证件, b.方案, b.开始日期, b.结束日期, b.餐饮消费汇总, 
        b.娱乐消费汇总, b.交通消费汇总, b.年度消费汇总, a.年度限额, b.Q1餐饮消费, b.Q1娱乐消费, 
        b.Q1交通消费, b.Q1餐饮消费.....
        from(
select 姓名,
    证件号,
    娱乐消费汇总,
    (case 娱乐消费汇总 when 0 then 25000 - sum(年度消费汇总) else 50000 - sum(年度消费汇总) end) as 年度限额
from 表
group by 姓名, 证件号
)a left join (
    select * from 表
)b on a.姓名 = b.姓名 and a.证件号 = b.证件号

 

select  a.姓名, a.证件号, a.方案, a.开始日期, a.结束日期, a.餐饮消费汇总, 
        a.娱乐消费汇总, a.交通消费汇总, a.年度消费汇总, b.年度限额
        from 示例2 a left join ( select 姓名,证件号,
         iif(sum(娱乐消费汇总)=0,25000-sum(年度消费汇总),50000-sum(年度消费汇总))  as 年度限额 
          from 示例2  group by 姓名, 证件号
         ) b on a.姓名 = b.姓名 and a.证件号 = b.证件号 order by a.姓名,a.娱乐消费汇总 desc

结果如下：

img



```sql
遇到判断得，加个case when  或者if 函数都行
select  b.姓名, b.证件, b.方案, b.开始日期,
           b.结束日期, b.餐饮消费汇总, 
          b.娱乐消费汇总, b.交通消费汇总, 
          b.年度消费汇总, a.年度限额, 
          b.Q1餐饮消费, b.Q1娱乐消费, 
          b.Q1交通消费, b.Q1餐饮消费.....
        from(
      select 姓名,证件号,娱乐消费汇总,
    (case 娱乐消费汇总 when 0 then 25000 - sum(年度消费汇总) else 50000 - sum(年度消费汇总) end) as 年度限额
from 表
group by 姓名, 证件号
)a left join (
    select * from 表
)b on a.姓名 = b.姓名 and a.证件号 = b.证件号

```