货品库存明细表如何用SQL实现 access(100分)

货品表<br>代码 &nbsp; 名称 &nbsp; &nbsp; &nbsp; &nbsp;期初数量 &nbsp;当前库存<br>001 &nbsp; 实验商品1 &nbsp; 100 &nbsp; &nbsp; &nbsp;2<br><br><br>单据主表<br>单号 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;单据日期 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>RK20080709 &nbsp; &nbsp;20080709 &nbsp; &nbsp; &nbsp; &nbsp;<br>CK20080710 &nbsp; &nbsp;20080709 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br>单据明细表 <br>单号 &nbsp; &nbsp; &nbsp; 货品代码 &nbsp; &nbsp;数量 &nbsp; &nbsp; &nbsp;<br>RK20080709 &nbsp;001 &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br>CK20080710 &nbsp;002 &nbsp; &nbsp; &nbsp; 100 &nbsp; &nbsp; &nbsp; &nbsp; <br><br>需要得到如下的报表<br>查询日期段为 2008-07-09 到 2008-07-10 &nbsp;货品为 实验商品1<br>开单日期 &nbsp; 入库 &nbsp; &nbsp;出库 &nbsp; &nbsp; 结存<br>20080709 &nbsp; 2 &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp;102<br>20080710 &nbsp; 0 &nbsp; &nbsp; 100 &nbsp; &nbsp; 2<br><br>数据库为 access 如何用sql实现<br>关键是结存数量的计算<br><br>望各位大虾出手相助<br>小弟在这儿多谢了

哦<br>修正一下<br>单据明细表的货品代码 全 为001

var huohao:string; //临时货号变量<br>&nbsp; &nbsp; s:double; //用于计算结存<br>ADOCommand1.CommandText:='select a.单据日期,b.货品代码,c.出库,d.入库,'<br>+'b.数量 as 结存 &nbsp;into &nbsp;#临时表 from 单据主表 a full join '<br>+'(select 单号,货品代码,数量 from 单据明细表) b ' on a.单号=b.单号 '<br>+' left join &nbsp;'<br>+'(select 单号,货品代码,数量 as 出库 单据明细表 where 状态 like ''出库'') c '<br>+' on ( &nbsp;c.货品代码=b.货品代码 and &nbsp;c.单号=b.单号 left join ) '<br>+' (select 单号,货品代码,数量 as 入库 单据明细表 where 状态 like ''出库'') d &nbsp;'<br>+' on ( &nbsp;d.货品代码=b.货品代码 and &nbsp;d.单号=b.单号) &nbsp;';<br>ADOCommand1.Execute; //得到临时表,但结存不正确,下一步是修改结存<br>ADODataSet1.CommandText:='select 开单日期,货品代码,sum(入库) as 入库, '<br>+'sum(出库) as 出库,sum(结存) as 结存 into #临时表1 from #临时表 '<br>+' group by &nbsp;开单日期,货品代码 order by 货品代码,单据日期 &nbsp;';<br>ADODataSet1.Execute; //合并入库和出库,并调整顺序<br>ADODataSet1.CommandText:='select * from #临时表1 ';<br>DataSource1.dataset:=ADODataSet1;<br>DBGrid.DataSource:=DataSource1;<br>ADODataSet1.open;<br><br>huohao:='';<br>s:=0;<br>DataSource1.DataSet.DisableControls;<br>DataSource1.DataSet.First;<br>while not DataSource1.DataSet.Eof do //利用DataSource游标修改 结存<br>begin<br>&nbsp;DataSource1.DataSet.Edit;<br>&nbsp;if huohao&lt;&gt;DataSource1.DataSet.Fields[1].AsString then <br>&nbsp; begin &nbsp;//Fields[1]是 货品代码 的值<br>&nbsp; &nbsp; s:=0;<br>&nbsp; &nbsp; huohao:=DataSource1.DataSet.Fields[1].AsString;<br>&nbsp; end &nbsp;<br>&nbsp;else<br>&nbsp; s:=s+StrToFloat(DataSource1.DataSet.Fields[1].AsString);<br><br>&nbsp; DataSource1.DataSet.Fields[4].AsString:=FloatToStr(s); //修改Fields[4]的值为s<br>DataSource.DataSet.Next;<br>end;<br>//我没测试过,思路是对的. where 时间没加进去