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

zhua · 发表于 2010-5-28 08:17

需求表

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

交叉表

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

zhua · 发表于 2010-5-28 08:46

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 · 发表于 2010-5-28 09:17

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

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

zhua · 发表于 2010-5-28 17:02

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

能否指点一下~

whsfhwm · 发表于 2010-5-28 22:30

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

Transform sum(合计) select 部门类型,科室 from(
select 部门类型,科室,物资类型,sum(合计)as 合计 from
( select 部门类型,供应商部门名称 as 科室,物资类型,sum(金额) as 合计 from [Cdata$] group by 部门类型,供应商部门名称 ,物资类型 union all
select 部门类型,供应商部门名称,物资类别 & '小计',sum(金额) from [Cdata$] group by 部门类型,供应商部门名称,物资类别 union all
select 部门类型,供应商部门名称,'总计',sum(金额) from [Cdata$] group by 部门类型,供应商部门名称 union all
select 部门类型&'小计','',物资类型,sum(金额) from [Cdata$] group by 部门类型,物资类型 union all
select 部门类型&'小计','',物资类别& '小计',sum(金额) from [Cdata$] group by 部门类型,物资类别 union all
select 部门类型&'小计','','总计',sum(金额) from [Cdata$] group by 部门类型 union all
select '总计',null,物资类型,sum(金额) from [Cdata$] group by 部门类型,物资类型 union all
select '总计',null,物资类别 & '小计',sum(金额) from [Cdata$] group by 部门类型,物资类别 union all
select '总计',null,'总计',sum(金额) from [Cdata$]
) group by 部门类型,科室,物资类型
) group by 部门类型,科室 order by 部门类型,科室 pivot 物资类型

复制代码

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

zhua · 发表于 2010-5-29 17:59

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

提示合计循环引用！

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

whsfhwm · 发表于 2010-5-29 20:30

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

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

Transform sum(合计) select Cdata.部门类型,科室 from(
select Cdata.部门类型,科室,Cdata.物资类型,sum(合计1)as 合计 from
( select Cdata.部门类型,Cdata.供应商部门名称 as 科室,Cdata.物资类型,sum(Cdata.金额) as 合计1 from Cdata group by Cdata.部门类型,Cdata.供应商部门名称 ,Cdata.物资类型 union all
select Cdata.部门类型,Cdata.供应商部门名称,Cdata.物资类别 & '小计',sum(Cdata.金额) from Cdata group by Cdata.部门类型,Cdata.供应商部门名称,Cdata.物资类别 union all
select Cdata.部门类型,Cdata.供应商部门名称,'总计',sum(Cdata.金额) from Cdata group by Cdata.部门类型,Cdata.供应商部门名称 union all
select Cdata.部门类型&'小计','',Cdata.物资类型,sum(Cdata.金额) from Cdata group by Cdata.部门类型,Cdata.物资类型 union all
select Cdata.部门类型&'小计','',Cdata.物资类别& '小计',sum(Cdata.金额) from Cdata group by Cdata.部门类型,Cdata.物资类别 union all
select Cdata.部门类型&'小计','','总计',sum(Cdata.金额) from Cdata group by Cdata.部门类型 union all
select '总计',null,Cdata.物资类型,sum(Cdata.金额) from Cdata group by Cdata.部门类型,Cdata.物资类型 union all
select '总计',null,Cdata.物资类别 & '小计',sum(Cdata.金额) from Cdata group by Cdata.部门类型,Cdata.物资类别 union all
select '总计',null,'总计',sum(Cdata.金额) from Cdata
) group by Cdata.部门类型,科室,Cdata.物资类型
) group by Cdata.部门类型,科室 order by Cdata.部门类型,科室 pivot Cdata.物资类型

复制代码

这里把我测试无误的结果也附上来，不过本人对ACCESS之前没有接触，所以不知道为什么运行之后查询表里再调出来的SQL语句就不是上面的摸样了。

Cdata.rar (11.28 KB, 下载次数: 101)

whsfhwm · 发表于 2010-5-30 09:35

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

zhua · 发表于 2010-5-30 09:13

[%$##@_Alias]

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

TRANSFORM Sum([%$##@_Alias].合计) AS 合计之总计
SELECT Cdata.部门类型 AS 表达式1, [%$##@_Alias].科室
FROM [select Cdata.部门类型,科室,Cdata.物资类型,sum(合计1)as 合计 from
( select Cdata.部门类型,Cdata.供应商部门名称 as 科室,Cdata.物资类型,sum(Cdata.金额) as 合计1 from Cdata group by Cdata.部门类型,Cdata.供应商部门名称 ,Cdata.物资类型 union all
select Cdata.部门类型,Cdata.供应商部门名称,Cdata.物资类别 & '小计',sum(Cdata.金额) from Cdata group by Cdata.部门类型,Cdata.供应商部门名称,Cdata.物资类别 union all
select Cdata.部门类型,Cdata.供应商部门名称,'总计',sum(Cdata.金额) from Cdata group by Cdata.部门类型,Cdata.供应商部门名称 union all
select Cdata.部门类型&'小计','',Cdata.物资类型,sum(Cdata.金额) from Cdata group by Cdata.部门类型,Cdata.物资类型 union all
select Cdata.部门类型&'小计','',Cdata.物资类别& '小计',sum(Cdata.金额) from Cdata group by Cdata.部门类型,Cdata.物资类别 union all
select Cdata.部门类型&'小计','','总计',sum(Cdata.金额) from Cdata group by Cdata.部门类型 union all
select '总计',null,Cdata.物资类型,sum(Cdata.金额) from Cdata group by Cdata.部门类型,Cdata.物资类型 union all
select '总计',null,Cdata.物资类别 & '小计',sum(Cdata.金额) from Cdata group by Cdata.部门类型,Cdata.物资类别 union all
select '总计',null,'总计',sum(Cdata.金额) from Cdata
) group by Cdata.部门类型,科室,Cdata.物资类型
]. AS [%$##@_Alias]
GROUP BY Cdata.部门类型, [%$##@_Alias].科室
ORDER BY Cdata.部门类型, [%$##@_Alias].科室
PIVOT Cdata.物资类型;

复制代码

代码也发生了变化！

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

zhua · 发表于 2010-5-30 12:58

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

		自动登录	找回密码
密码			免费注册

[求助] 交叉表查询能否解决列小计和行小计？