access查询问题

[dellxng2000]

原始表格TABLE A如下，希望返回的结果是TABLE B,用ACCESS怎么实现这个功能？谢谢
TABLE A TABLE B
pod cntrtype POD 20 40
BBBGI 40 BBBGI 1
CAVAN 20 CAVAN 1 3
CAVAN 40 CNDAL 2 1
CAVAN 40 GBFLX 1
CAVAN 40 GRPIE 1
CNDAL 20 HKHKG 2 2
CNDAL 40 HTPAP 1
CNDAL 20 ILHFA 2 1
GBFLX 40 PABLB 1
GRPIE 40 PHMNT 1 2
HKHKG 20 PHZMP 1
HKHKG 40 PRSJU 2
HKHKG 40 SRPRB 1
HKHKG 20 TTPFS 1
HTPAP 20 USLAX 1
ILHFA 20 USMOB 1 1
ILHFA 40
ILHFA 20
PABLB 20
PHMNT 20
PHMNT 40
PHMNT 40
PHZMP 20
PRSJU 40
PRSJU 40
SRPRB 20
TTPFS 20
USLAX 20
USMOB 20
USMOB 40

[dellxng2000]

最后一行加个“合计”，怎么写呢？谢谢了

[ACMAIN_CHM]

select pod,
	sum(iif( cntrtype=20,1,0)) as [20],
	sum(iif( cntrtype=40,1,0)) as [40]
from A
group by pod

QQ群 48866293 / 12035577 / 7440532 / 13666209
https://forum.csdn.net/BList/OtherDatabase .
http://www.accessbbs.cn/bbs/index.php .
http://www.accessoft.com/bbs/index.asp .
http://www.access-programmers.co.uk/forums .
http://www.office-cn.net .
.
http://www.office-cn.net/home/space.php?uid=141646 .

[WWWWA]

transform count(*)
select pod from tt group by pod
pivot cntrtype

[dellxng2000]

格式有点乱，重新贴一遍TABLE A ,TABLE B
TABLE A
pod cntrtype
BBBGI 40
CAVAN 20
CAVAN 40
CAVAN 40
CAVAN 40
CNDAL 20
CNDAL 40
CNDAL 20
GBFLX 40
GRPIE 40
HKHKG 20
HKHKG 40
HKHKG 40
HKHKG 20
HTPAP 20
ILHFA 20
ILHFA 40
ILHFA 20
PABLB 20
PHMNT 20
PHMNT 40
PHMNT 40
PHZMP 20
PRSJU 40
PRSJU 40
SRPRB 20
TTPFS 20
USLAX 20
USMOB 20
USMOB 40

TABLE B
POD 20 40
BBBGI 1
CAVAN 1 3
CNDAL 2 1
GBFLX 1
GRPIE 1
HKHKG 2 2
HTPAP 1
ILHFA 2 1
PABLB 1
PHMNT 1 2
PHZMP 1
PRSJU 2
SRPRB 1
TTPFS 1
USLAX 1
USMOB 1 1

[WWWWA]

transform count(*)
select pod from tt group by pod
pivot cntrtype

也可以用SUM(IIF())，事先要知道cntrtype中的内容

[ACMAIN_CHM]

方法有多种，

1。 sum(iif
2。 两个表然后 left jon
3。 交叉表。transform


QQ群 48866293 / 12035577 / 7440532 / 13666209
https://forum.csdn.net/BList/OtherDatabase .
http://www.accessbbs.cn/bbs/index.php .
http://www.accessoft.com/bbs/index.asp .
http://www.access-programmers.co.uk/forums .
http://www.office-cn.net .
.
http://www.office-cn.net/home/space.php?uid=141646 .

[dellxng2000]

table A 的内容我是这样得到的
SELECT sheet1.pod, IIf(sheet1.teu=2,40,20) AS cntrtype, sheet1.teu
FROM [select sheet1.pod,sheet1.[cnt tp],sheet1.teu
from sheet1
GROUP BY sheet1.pod,sheet1.[cnt tp],sheet1.teu]

但是我套用进去不行，怎样修改呢？

select sheet1.pod,
sum(iif( cntrtype=20,1,0)) as [20],
sum(iif( cntrtype=40,1,0)) as [40]
from
(SELECT sheet1.pod, IIf(sheet1.teu=2,40,20) AS cntrtype, sheet1.teu
FROM [select sheet1.pod,sheet1.[cnt tp],sheet1.teu
from sheet1
GROUP BY sheet1.pod,sheet1.[cnt tp],sheet1.teu]. AS [%$##@_Alias])
group by sheet1.pod

[WWWWA]

select sheet1.pod,
sum(iif( cntrtype=20,1,0)) as [20],
sum(iif( cntrtype=40,1,0)) as [40]
from
(SELECT sheet1.pod, IIf(sheet1.teu=2,40,20) AS cntrtype, sheet1.teu
FROM (select sheet1.pod,sheet1.[cnt tp],sheet1.teu
from sheet1
GROUP BY sheet1.pod,sheet1.[cnt tp],sheet1.teu))
group by sheet1.pod

[ACMAIN_CHM]

select pod, 
    sum(iif( cntrtype=20,1,0)) as [20], 
    sum(iif( cntrtype=40,1,0)) as [40] 
from (SELECT pod, IIf(teu=2,40,20) AS cntrtype, teu 
	FROM sheet1 ) 
group by pod 

QQ群 48866293 / 12035577 / 7440532 / 13666209
https://forum.csdn.net/BList/OtherDatabase .
http://www.accessbbs.cn/bbs/index.php .
http://www.accessoft.com/bbs/index.asp .
http://www.access-programmers.co.uk/forums .
http://www.office-cn.net .
.
http://www.office-cn.net/home/space.php?uid=141646 .

[ACMAIN_CHM]

select pod, 
    sum(iif( cntrtype=2,1,0)) as [20], 
    sum(iif( cntrtype=2,0,1)) as [40] 
from sheet1
group by pod 

[linguangfei2007]

顶一下

[ACMAIN_CHM]

[Quote=引用 11 楼 dellxng2000 的回复:]
最后一行加个“合计”，怎么写呢？谢谢了
[/Quote]

select pod, 
    sum(iif( cntrtype=2,1,0)) as [20], 
    sum(iif( cntrtype=2,0,1)) as [40] 
from sheet1
group by pod 
union all
select 'Total' as pod, 
    sum(iif( cntrtype=2,1,0)) as [20], 
    sum(iif( cntrtype=2,0,1)) as [40] 
from sheet1

QQ群 48866293 / 12035577 / 7440532 / 13666209
https://forum.csdn.net/BList/OtherDatabase .
http://www.accessbbs.cn/bbs/index.php .
http://www.accessoft.com/bbs/index.asp .
http://www.access-programmers.co.uk/forums .
http://www.office-cn.net .
.
http://www.office-cn.net/home/space.php?uid=141646 .