|
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));
|
|