交叉表查询能否解决列小计和行小计？

zhua

需求表

以上报表用透视表很容易在excel中做出，但在access中呢？如何查询解决？
一开始联想到用交叉表，只能得到如下结果。

交叉表

行小计和类小计无法解决。
请问Access中真的无法处理么？
还有没有其他的解决方法？

zhua

http://access911.net/fixhtm/72FAB31E16DCE8F3.htm?tt=如何在表中同时显示明细、小计、总计？《查询》
根据以上方法，只做出一下内容

select  Cdata_交叉表.部门类型,Cdata_交叉表.总计金额  as 合计 from Cdata_交叉表
union all
SELECT Cdata_交叉表.部门类型 & " 小计" AS 表达式1, Sum(Cdata_交叉表.总计金额) AS 合计
FROM Cdata_交叉表
GROUP BY Cdata_交叉表.部门类型 & " 小计";
UNION ALL
select  "总计", Sum(Cdata_交叉表.总计金额) AS 合计 from Cdata_交叉表;

whsfhwm

此前本人最多只能做到第一个表中，除了最右下角的那个单元格没有数据，其余都能做出来，有点复杂。暂时没时间:)

[ 本帖最后由 whsfhwm 于 2010-5-28 22:49 编辑 ]

zhua

原帖由 whsfhwm 于 2010-5-28 09:17 发表
做多只能做到第一个表中，除了最右下角的那个单元格没有数据，其余都能做出来，有点复杂。暂时没时间:)

能否指点一下~

whsfhwm

Cdata.rar (8.58 KB, 下载次数: 63)

2010-5-28 22:30 上传

点击文件名下载附件

本题对我来说还是很有难度的 :)
水平有限，纠结了一个晚上，终于差不多了，虽然有一点小瑕疵（总计在最上面一行了）
SQL语句超复杂：不知道有没有高手看看简化一下。

1. Transform sum(合计) select 部门类型,科室 from(
   
2.   select 部门类型,科室,物资类型,sum(合计)as 合计 from
   
3.     (  select  部门类型,供应商部门名称 as 科室,物资类型,sum(金额)  as 合计 from [Cdata$]  group by 部门类型,供应商部门名称 ,物资类型 union all   
   
4.      select  部门类型,供应商部门名称,物资类别 & '小计',sum(金额) from [Cdata$] group by 部门类型,供应商部门名称,物资类别 union all   
   
5.      select  部门类型,供应商部门名称,'总计',sum(金额) from [Cdata$] group by 部门类型,供应商部门名称 union all
   
6.      select  部门类型&'小计','',物资类型,sum(金额) from [Cdata$] group by 部门类型,物资类型 union all   
   
7.      select  部门类型&'小计','',物资类别& '小计',sum(金额) from [Cdata$] group by 部门类型,物资类别 union all
   
8.      select  部门类型&'小计','','总计',sum(金额) from [Cdata$] group by 部门类型  union all   
   
9.      select  '总计',null,物资类型,sum(金额) from [Cdata$] group by 部门类型,物资类型 union all
   
10.      select  '总计',null,物资类别 & '小计',sum(金额) from [Cdata$] group by 部门类型,物资类别 union all
    
11.      select  '总计',null,'总计',sum(金额) from [Cdata$]      
    
12.     ) group by 部门类型,科室,物资类型
    
13. )  group by 部门类型,科室 order by 部门类型,科室  pivot 物资类型

复制代码

[ 本帖最后由 whsfhwm 于 2010-5-28 22:48 编辑 ]

zhua

首先谢谢楼上的回答。
测试了下有如下问题，。
我的本意是想在access中作查询，上传个excel是为了展示要得到的结果。楼上的可能误会了！
我把代码放到了access查询器中结果不对，有问题，请各位分析下。怎么解决！

提示合计循环引用！

[ 本帖最后由 zhua 于 2010-5-29 18:05 编辑 ]

whsfhwm

原帖由 zhua 于 2010-5-29 17:59 发表
首先谢谢楼上的回答。
测试了下有如下问题，。
我的本意是想在access中作查询，上传个excel是为了展示要得到的结果。楼上的可能误会了！
我把代码放到了access查询器中结果不对，有问题，请各位分析下。怎么解决！ ...

EXCEL与ACCESS中的SQL基本通用，细节处略作修改即可。
经测试，下面的语句在ACCESS中运行无误，最后把查询结果的列字段手工移动到期望的结果即可。

1. Transform sum(合计) select Cdata.部门类型,科室 from(
   
2.   select Cdata.部门类型,科室,Cdata.物资类型,sum(合计1)as 合计 from
   
3.     (  select  Cdata.部门类型,Cdata.供应商部门名称 as 科室,Cdata.物资类型,sum(Cdata.金额)  as 合计1 from  Cdata   group by Cdata.部门类型,Cdata.供应商部门名称 ,Cdata.物资类型 union all   
   
4.      select  Cdata.部门类型,Cdata.供应商部门名称,Cdata.物资类别 & '小计',sum(Cdata.金额) from  Cdata  group by Cdata.部门类型,Cdata.供应商部门名称,Cdata.物资类别 union all   
   
5.      select  Cdata.部门类型,Cdata.供应商部门名称,'总计',sum(Cdata.金额) from  Cdata  group by Cdata.部门类型,Cdata.供应商部门名称 union all
   
6.      select  Cdata.部门类型&'小计','',Cdata.物资类型,sum(Cdata.金额) from  Cdata  group by Cdata.部门类型,Cdata.物资类型 union all   
   
7.      select  Cdata.部门类型&'小计','',Cdata.物资类别& '小计',sum(Cdata.金额) from  Cdata  group by Cdata.部门类型,Cdata.物资类别 union all
   
8.      select  Cdata.部门类型&'小计','','总计',sum(Cdata.金额) from  Cdata  group by Cdata.部门类型  union all   
   
9.      select  '总计',null,Cdata.物资类型,sum(Cdata.金额) from  Cdata  group by Cdata.部门类型,Cdata.物资类型 union all
   
10.      select  '总计',null,Cdata.物资类别 & '小计',sum(Cdata.金额) from  Cdata  group by Cdata.部门类型,Cdata.物资类别 union all
    
11.      select  '总计',null,'总计',sum(Cdata.金额) from  Cdata        
    
12.     ) group by Cdata.部门类型,科室,Cdata.物资类型
    
13. )  group by Cdata.部门类型,科室 order by Cdata.部门类型,科室  pivot Cdata.物资类型

复制代码

这里把我测试无误的结果也附上来，不过本人对ACCESS之前没有接触，所以不知道为什么运行之后查询表里再调出来的SQL语句就不是上面的摸样了。
Cdata.rar (11.28 KB, 下载次数: 101)

2010-5-29 20:30 上传

点击文件名下载附件

whsfhwm

列顺序手工调整，之后再刷新也不会发生变化。

zhua

[%$##@_Alias]

代码中出现了以上的字符，
顺序也发生了变化！部门类型和科室列都调到右边去了！

1. TRANSFORM Sum([%$##@_Alias].合计) AS 合计之总计
   
2. SELECT Cdata.部门类型 AS 表达式1, [%$##@_Alias].科室
   
3. FROM [select Cdata.部门类型,科室,Cdata.物资类型,sum(合计1)as 合计 from
   
4.     (  select  Cdata.部门类型,Cdata.供应商部门名称 as 科室,Cdata.物资类型,sum(Cdata.金额)  as 合计1 from  Cdata   group by Cdata.部门类型,Cdata.供应商部门名称 ,Cdata.物资类型 union all   
   
5.      select  Cdata.部门类型,Cdata.供应商部门名称,Cdata.物资类别 & '小计',sum(Cdata.金额) from  Cdata  group by Cdata.部门类型,Cdata.供应商部门名称,Cdata.物资类别 union all   
   
6.      select  Cdata.部门类型,Cdata.供应商部门名称,'总计',sum(Cdata.金额) from  Cdata  group by Cdata.部门类型,Cdata.供应商部门名称 union all
   
7.      select  Cdata.部门类型&'小计','',Cdata.物资类型,sum(Cdata.金额) from  Cdata  group by Cdata.部门类型,Cdata.物资类型 union all   
   
8.      select  Cdata.部门类型&'小计','',Cdata.物资类别& '小计',sum(Cdata.金额) from  Cdata  group by Cdata.部门类型,Cdata.物资类别 union all
   
9.      select  Cdata.部门类型&'小计','','总计',sum(Cdata.金额) from  Cdata  group by Cdata.部门类型  union all   
   
10.      select  '总计',null,Cdata.物资类型,sum(Cdata.金额) from  Cdata  group by Cdata.部门类型,Cdata.物资类型 union all
    
11.      select  '总计',null,Cdata.物资类别 & '小计',sum(Cdata.金额) from  Cdata  group by Cdata.部门类型,Cdata.物资类别 union all
    
12.      select  '总计',null,'总计',sum(Cdata.金额) from  Cdata        
    
13.     ) group by Cdata.部门类型,科室,Cdata.物资类型
    
14. ]. AS [%$##@_Alias]
    
15. GROUP BY Cdata.部门类型, [%$##@_Alias].科室
    
16. ORDER BY Cdata.部门类型, [%$##@_Alias].科室
    
17. PIVOT Cdata.物资类型;
    

复制代码

代码也发生了变化！

[ 本帖最后由 zhua 于 2010-5-30 09:17 编辑 ]

zhua

但是部门类型，这列，列名会变声表达式1