联合查询问题？？

cdwlove

在联合查询中多加入一个废品字段，为何总是出现“在联合查询中所选定的两个数据表或查询中的列数不匹配！” 应该怎么改？谢谢！！

hi-wzj

SELECT 材料ID,期初库存 AS 期初, 0 AS 入库, 0 AS 出库, 0 AS 退出 , 0 AS 废品  FROM 材料表, 期初所指日期;

UNION ALL SELECT 材料ID, IIf([Forms]![zcd]![qsr]>[期初所指日期]![日期],[入库数量],IIf([Forms]![zcd]![qsr]=[期初所指日期]![日期],0,IIf([入库单]![日期]=[期初所指日期]![日期],0,-1*[入库数量]))) AS 表达式1, 0 AS 表达式2, 0 AS 表达式3, 0 AS 表达式4,0
FROM 期初所指日期, 入库单 INNER JOIN 入库单明细 ON 入库单.单据ID = 入库单明细.单据ID
WHERE (((入库单.日期) Between [Forms]![zcd]![qsr] And [期初所指日期]![日期]));

union all SELECT 材料ID, IIf([Forms]![zcd]![qsr]>[期初所指日期]![日期],[领料数量],IIf([Forms]![zcd]![qsr]=[期初所指日期]![日期],0,IIf([领料单]![日期]=[期初所指日期]![日期],0,[领料数量]))) AS 表达式1, 0 AS 表达式2, 0 AS 表达式3, 0 AS 表达式4,0
FROM 期初所指日期, 领料单 INNER JOIN 领料单明细 ON 领料单.单据ID = 领料单明细.单据ID
WHERE (((领料单.日期) Between [Forms]![zcd]![qsr] And [期初所指日期]![日期]));


UNION ALL SELECT 材料ID,0,入库数量,0,0,0
FROM 入库单 INNER JOIN 入库单明细 ON 入库单.单据ID = 入库单明细.单据ID
WHERE 日期 Between [Forms]![zcd]![qsr] And [Forms]![zcd]![jsr] AND 性质<>2 and 性质<>5;
   
UNION ALL SELECT 材料ID, 0, 0,领料数量, 0,0
FROM 领料单 INNER JOIN 领料单明细 ON 领料单.单据ID = 领料单明细.单据ID
WHERE 日期 Between [Forms]![zcd]![qsr] And [Forms]![zcd]![jsr];


UNION ALL SELECT 材料ID,0,0,0,入库数量,0
FROM 入库单 INNER JOIN 入库单明细 ON 入库单.ID = 入库单明细.ID
WHERE (((入库单.日期) Between [Forms]![zcd]![qsr] And [Forms]![zcd]![jsr]) AND ((入库单.性质)=2));

UNION ALL SELECT 材料ID,0,0,0,入库数量,0
FROM 入库单 INNER JOIN 入库单明细 ON 入库单.ID = 入库单明细.ID
WHERE (((入库单.日期) Between [Forms]![zcd]![qsr] And [Forms]![zcd]![jsr]) AND ((入库单.性质)=5));

cdwlove

hi-wzj版主，引用了你的代码为什么会有重复的！！可以帮我改改那个例子吗？？谢谢！！

cdwlove

hi-wzj版主，引用了你的代码还是不行呀，数据到了退出一栏，而没有到废品栏！

cdwlove

自己顶！！

120HZQ

-wzj版主，引用了你的代码为什么会有重复的！！可以帮我改改那个例子吗？？谢谢！！