报表问题！

[Holmescn]

我现在有两个表，在access 2003下
[每日收入]
ID 自动计数
收入日期 日期
收入金额 货币
收入类型 文本
收入备注 文本

[每日支出]
ID 自动计数
支出日期 日期
支出金额 货币
支出类型 文本
支出备注 文本


我现在要对这两个表做一个统一的报表，如下：
日期 按月
日期 收入金额 收入类型 支出金额 支出类型

明细…………

汇总

收支平衡



请问应该怎么做呢。我用了好多个关系，但是总是不正确，汇总有问题，请指点！

[Holmescn]

请教高手！！！

[lw1a2]

access 支持Case语句吗？

[vbman2003]

select format(dates,"YYYY-MM-DD") as [date],ea,ea_cat,0 as pay,'' as pay_cat
from ea_info
UNION
select format(dates,"YYYY-MM-DD"),0 as ea,'' as ea_cat,pay,pay_cat
from pay_info
UNION
select 'SUM:',sum(ea) as sum_ea,'',sum(pay) as sum_pay,''
from ea_info,pay_info

en ----> 收入
pay------>支出
dates-------->日期

[vbman2003]

access 不支持Case语句吗，支持iif函数

[vbman2003]

上面的合计有问题，你改如下试试：
select format(dates,"YYYY-MM-DD") as [date],ea,ea_cat,0 as pay,'' as pay_cat
from ea_info
UNION
select format(dates,"YYYY-MM-DD"),0 as ea,'',pay,pay_cat
from pay_info
UNION select 'SUM:',sum_ea,'',sum_pay,''
from (select sum(ea) as sum_ea from ea_info) a ,(select sum(pay) as sum_pay from pay_info) b

[wwwwb]

TRY:
SELECT A.*,B.* FROM
(SELECT 收入日期 AS 日期,收入类型,SUM(收入金额) FROM B1 GROUP BY 收入日期,收入类型) A INNER JOIN
(SELECT 支出日期 AS 日期,支出类型,SUM(支出金额) FROM B2 GROUP BY 支出日期,支出类型) B
ON A.日期=B.日期

[wwwwb]

上述是合计，明细：
select a.*,b.* from
(select 收入日期 AS 日期,收入类型,收入金额 as srje,FROM B1 ) a left join
(select 支出日期 AS 日期,支出类型,支出金额 as zcje FROM B1) b on a. 日期=B.日期
union
SELECT A.*,B.* FROM
(SELECT 收入日期 AS 日期,收入类型,SUM(收入金额) as srje,0 as zcje FROM B1 GROUP BY 收入日期,收入类型) A left JOIN
(SELECT 支出日期 AS 日期,支出类型,0 as srje,SUM(支出金额) as zcje FROM B2 GROUP BY 支出日期,支出类型) B
ON A.日期=B.日期

[wwwwb]

select a.*,b.* from
(select 收入日期 AS 日期,收入类型,收入金额 as srje,FROM B1 ) a left join
(select 支出日期 AS 日期,支出类型,支出金额 as zcje FROM B1) b on a. 日期=B.日期
union
SELECT A.*,B.* FROM
(SELECT 收入日期 AS 日期,收入类型,' ' as 支出类型,SUM(收入金额) as srje,0 as zcje FROM B1 GROUP BY 收入日期,收入类型) A left JOIN
(SELECT 支出日期 AS 日期,' ' as 收入类型,支出类型,0 as srje,SUM(支出金额) as zcje FROM B2 GROUP BY 支出日期,支出类型) B
ON A.日期=B.日期