计算库存的语句能这样写吗？

三张表
进货表
出货表
销售表

表的结构是一样的

商品名称，商品编码，单位，数量，单价，进货日期

                                      出货日期
                                      销售日期
我要查的是
商品名称，商品编码，单位，单价，上月结存数，上月结存金额，本月进货数，本月进货金额，本月出货数，本月出货金额，本月销售数，本月销售金额，本月结余数，本月结余金额

 kccx:='SELECT cgmx.spbm as spbm,cgmx.spmc as spmc,cgmx.jldw as dw,cgmx.price as price,'
        +' (select (select sum(cgmx.number) where cgrq<Datetimepicker1)
        +' -IIf(IsNull(select sum(spxs.number) from spxs where xsrq<DateTimepicker1),0,sum(spxs.number)
        +'-IIf(IsNull(select sum(spck.number) from spck where ckrq<DateTimepicker1),0,sum(spck.number))'
        +' FROM cgmx LEFT JOIN spxs ON cgmx.spbm=spxs.spbm
        + left join spck on cgmx.spbm=spck.spbm) AS jcsl'
        +' price*jcsl as jcje'
        +' (select spbm,sum(number) from cgmx group by spbm) as jhsl'
        +' price*jhsl as jhje'
        +' (select spbm,sum(number) from spck group by spbm) as chsl'
        +' price*chsl as chje'
        +' (select spbm,sum(number) from spxs group by spbm) as xssl'
        +' price*xssl as xsje'
        +' (jcsl+jhsl-chsl-xssl) as jysl'
        +' price*jysl as jyje'
        +' from cgmx left join spck on cgmx.spbm=spck.spbm'
        +' left join spxs on cgmx.spbm=spxs.spbm'
        +' where between #'+formatdatetime('yyyy-mm-dd',DateTimePicker1.datetime)+'# and #'+formatdatetime('yyyy-mm-dd',DateTimePicker2.datetime)+'#'
        +' group by cgmx.spmc,cgmx.spbm,cgmx.jldw';

大家帮我看看我这个查询语句哪错了

或者帮我换一种简洁点的语句

谢谢大家了