Office中国论坛/Access中国论坛

标题: 两表怎么样实现合并！具体看附件 [打印本页]

作者: accessNew001 时间: 2005-12-29 06:11
标题: 两表怎么样实现合并！具体看附件
2004与2005年的销售情况

怎么样样实现2004VS2005 还有对字体颜色和底色的控制要求2005年的记录全是红色字绿色底怎么实现呢

还有最重要的是2004的customer 与2005的customer 怎么样合并呢？

[attach]15026[/attach]

先谢谢了！
[em03][em03][em03]

[此贴子已经被作者于2005-12-28 23:08:22编辑过]

作者: 阿笨 时间: 2005-12-29 08:35
我这里没法存附件,所以说的只供参考:

查询里面用select into customer *,2004 as 年份 from customer2004 UNION select *,2005 as 年份 from customer2005

然后用条件格式查看

作者: accessNew001 时间: 2005-12-29 16:30
还是不可以哦

运行不出来

[em03][em03][em03][em03]

作者: wwwwa 时间: 2005-12-29 16:50
思路：

对两表分别进行交叉操作，生成交叉表，在这个帖子已经回答： http://www.office-cn.net/forum.php?mod=viewthread&tid=34816&replyID=173779&skin=1

再用LEFT JOIN将两表连接起来即可。

作者: gzh97730 时间: 2005-12-29 16:53
建两个查询表:

表一：联合两表的数据

表二：按楼主的格式显示数据

表一<2004Union2005>：

SELECT Customer,date,month(Date) as month, Quantity, 4 AS [year]
FROM Invsale2004
UNION SELECT Customer,date,month(Date) as month, Quantity, 5 AS [year]
FROM Invsale2005;

表二：

TRANSFORM Sum(Quantity) AS sum
SELECT Customer
FROM 2004Union2005
GROUP BY Customer
ORDER BY Format([month],"00") & [year] & Format([date],"mmm")
PIVOT Format([month],"00") & [year] & Format([date],"mmm");

剩下的基本都是由excel来处理了。时间关系不列出来，或者哪位网友可补一下。

可以问一下，楼主是什么职业？

作者: wwwwa 时间: 2005-12-29 18:00
SELECT *
FROM [SELECT Customer,sum(iif(month([Date])=1,Quantity,0)) as 1,sum(iif(month([Date])=2,Quantity,0)) as 2,
sum(iif(month([Date])=3,Quantity,0)) as 3,sum(iif(month([Date])=4,Quantity,0)) as 4,
sum(iif(month([Date])=5,Quantity,0)) as 5,sum(iif(month([Date])=6,Quantity,0)) as 6,
sum(iif(month([Date])=7,Quantity,0)) as 7,sum(iif(month([Date])=6,Quantity,0)) as 8,
sum(iif(month([Date])=9,Quantity,0)) as 9,sum(iif(month([Date])=6,Quantity,0)) as 10,
sum(iif(month([Date])=11,Quantity,0)) as 11,sum(iif(month([Date])=6,Quantity,0)) as 12,
[1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12] as hj
FROM Invsale2004 group by Customer union
select '合计' as Customer,sum(iif(month([Date])=1,Quantity,0)) as 1,sum(iif(month([Date])=2,Quantity,0)) as 2,
sum(iif(month([Date])=3,Quantity,0)) as 3,sum(iif(month([Date])=4,Quantity,0)) as 4,
sum(iif(month([Date])=5,Quantity,0)) as 5,sum(iif(month([Date])=6,Quantity,0)) as 6,
sum(iif(month([Date])=7,Quantity,0)) as 7,sum(iif(month([Date])=6,Quantity,0)) as 8,
sum(iif(month([Date])=9,Quantity,0)) as 9,sum(iif(month([Date])=6,Quantity,0)) as 10,
sum(iif(month([Date])=11,Quantity,0)) as 11,sum(iif(month([Date])=6,Quantity,0)) as 12,
[1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12] as hj
FROM Invsale2004 ]. AS a LEFT JOIN [SELECT Customer,sum(iif(month([Date])=1,Quantity,0)) as 1,sum(iif(month([Date])=2,Quantity,0)) as 2,
sum(iif(month([Date])=3,Quantity,0)) as 3,sum(iif(month([Date])=4,Quantity,0)) as 4,
sum(iif(month([Date])=5,Quantity,0)) as 5,sum(iif(month([Date])=6,Quantity,0)) as 6,
sum(iif(month([Date])=7,Quantity,0)) as 7,sum(iif(month([Date])=6,Quantity,0)) as 8,
sum(iif(month([Date])=9,Quantity,0)) as 9,sum(iif(month([Date])=6,Quantity,0)) as 10,
sum(iif(month([Date])=11,Quantity,0)) as 11,sum(iif(month([Date])=6,Quantity,0)) as 12,
[1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12] as hj
FROM Invsale2005 group by Customer union
select '合计' as Customer,sum(iif(month([Date])=1,Quantity,0)) as 1,sum(iif(month([Date])=2,Quantity,0)) as 2,
sum(iif(month([Date])=3,Quantity,0)) as 3,sum(iif(month([Date])=4,Quantity,0)) as 4,
sum(iif(month([Date])=5,Quantity,0)) as 5,sum(iif(month([Date])=6,Quantity,0)) as 6,
sum(iif(month([Date])=7,Quantity,0)) as 7,sum(iif(month([Date])=6,Quantity,0)) as 8,
sum(iif(month([Date])=9,Quantity,0)) as 9,sum(iif(month([Date])=6,Quantity,0)) as 10,
sum(iif(month([Date])=11,Quantity,0)) as 11,sum(iif(month([Date])=6,Quantity,0)) as 12,
[1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12] as hj
FROM Invsale2005 ]. AS b ON a.Customer=b.Customer;

作者: accessNew001 时间: 2005-12-30 05:58
谢谢WWWWa 你真是个有心人

但是用left jion 有个问题哦就是如果2004 与2005customer 中的字段不相等就不能查询出来哦

我现在要所有customer

方法很好

作者: accessNew001 时间: 2005-12-30 06:03

以下是引用gzh97730在2005-12-29 8:53:00的发言：

建两个查询表:

表一：联合两表的数据

表二：按楼主的格式显示数据

表一<2004Union2005>：

SELECT Customer,date,month(Date) as month, Quantity, 4 AS [year]
FROM Invsale2004
UNION SELECT Customer,date,month(Date) as month, Quantity, 5 AS [year]
FROM Invsale2005;

表二：

TRANSFORM Sum(Quantity) AS sum
SELECT Customer
FROM 2004Union2005
GROUP BY Customer
ORDER BY Format([month],"00") & [year] & Format([date],"mmm")
PIVOT Format([month],"00") & [year] & Format([date],"mmm");

剩下的基本都是由excel来处理了。时间关系不列出来，或者哪位网友可补一下。

可以问一下，楼主是什么职业？

多谢gzh97730

我是公司的一个文员

数据处理这方面刚刚接触很多都不懂！

作者: wwwwa 时间: 2005-12-30 16:06

以下是引用accessNew001在2005-12-29 21:58:00的发言：

谢谢WWWWa 你真是个有心人

但是用left jion 有个问题哦就是如果2004 与2005customer 中的字段不相等就不能查询出来哦

我现在要所有customer

方法很好

JET SQL 中不支持 FULL JOIN （OUT JOIN 外连接），你可以用Left (Outer) Join和Right (Outer) Join，然后用 Union All 连接两个结果集，来达到外连接的效果。

作者: 看上去很虚幻 时间: 2006-1-9 14:44
wwwwa眼晕

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)