一个简单的材料出入库管理系统，适合小型电子装配企业

1、先建一个联合查询，命名为“联合查询”：
select format(日期,"yyyymmdd") & "A" & "00000000" as ID,日期,料号,期初库存量 as 期初,0 as 入库,0 as 出库
from 期初库存
union all select format(日期,"yyyymmdd") & "B" & format(YID,"00000000") as ID,日期,料号, 0 as 期初,数量 as 入库,0 as 出库
FROM YCR
UNION ALL select format(日期,"yyyymmdd") & "C" & format(LID,"00000000") as ID,日期,料号,0 as 期初,0 as 入库,实发数量 as 出库
from YLL
ORDER BY ID;

2、建立收发存日查询，命名为“收发存查询”：
SELECT a.ID, a.日期, a.料号, b.品名, b.规格, b.单位, nz(DSum("期初+入库-出库","联合查询","料号='" & a.料号 & "' and ID<'" & [ID] & "'"),0) AS 期初, a.入库, a.出库, DSum("期初+入库-出库","联合查询","料号='" & a.料号 & "' and ID<='" & [ID] & "'") AS 期末
FROM 联合查询 as a INNER JOIN YCL as b ON a.料号 = b.料号
ORDER BY a.料号, a.ID;

3、建立收发存月度查询，命名为“收发存月查询”：
SELECT Format(a.[日期],"yyyy/mm") AS 月度,a.料号,a.品名,a.规格,a.单位, First(a.期初) AS 期初, Sum(a.入库) AS 入库, Sum(a.出库) AS 出库, Last(a.期末) AS 期末
FROM 收发存查询 as a
GROUP BY Format(a.[日期],"yyyy/mm"), a.料号, a.品名, a.规格, a.单位;