两表怎么样实现合并！具体看附件

accessNew001

2004与2005年的销售情况

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

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



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

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

阿笨

我这里没法存附件,所以说的只供参考:

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

然后用条件格式查看

accessNew001

还是不可以哦

运行不出来

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

wwwwa

思路：

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

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

gzh97730

建两个查询表:

表一：联合两表的数据

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

表一<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

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

谢谢WWWWa  你真是个有心人

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

我现在要所有customer

方法很好

accessNew001

以下是引用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

以下是引用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 连接两个结果集，来达到外连接的效果。

看上去很虚幻

wwwwa眼晕