多表查询汇总问题!

[llq0240]

有八个表,表的结构如下

--创建货品信息表
create table T_Goods(
id int IDENTITY(1,1) primary key, --编号
Barcode varchar(50), --条形码
GoodsCode varchar(50), --货品编码
GoodsName varchar(200), --货品名称
GoodsType varchar(50), --货品类别 /code
GoodsUnit varchar(50), --货品规格
Units varchar(10), --单位
price float, --单价
vipprice float, --会员价
DepotUp float, --库存上限
DepotDown float, --库存下限
memo varchar(200)) --备注

--创建仓库货品库存表
create table depot(
depotname varchar(50), --仓库名称
Barcode varchar(50), --条形码
GoodsName varchar(200), --货品名称
GoodsCode varchar(50), --货品编码
units varchar(10), --单位
Number float) --数量


--创建采购货品入库表
create table T_Import(
id int IDENTITY(1,1) primary key, --编号
ImportID varchar(50), --入库单号
depotname varchar(50), --仓库名称
Barcode varchar(50), --条形码
GoodsCode varchar(50), --货品编码
GoodsName varchar(200), --货品名称
GoodsUnit varchar(50), --货品规格
units varchar(10), --单位
Number float, --数量
InPrice float, --入库价
Outdate varchar(200), --过期时间
Product varchar(200), --供应商
OnBillCode varchar(50), --对应的单号
memo varchar(200), --备注
jingbanren varchar(50), --经办人
zhizuoren varchar(50), --制作人
Addtime datetime) --入库时间

--创建采购货品退货表
create table T_Outport(
id int IDENTITY(1,1) primary key, --编号
OutportID VARCHAR(50), --退货单号
depotname varchar(50), --仓库名称
Barcode varchar(50), --条形码
GoodsCode varchar(50), --货品编码
GoodsName varchar(200), --货品名称
GoodsUnit varchar(50), --货品规格
units varchar(10), --单位
Number float, --数量
InPrice float, --入库价
Outdate varchar(200), --过期时间
Product varchar(200), --供应商
OnBillCode varchar(50), --对应的入库单号
memo varchar(200), --备注
jingbanren varchar(50), --经办人
zhizuoren varchar(50), --制作人
Addtime smalldatetime) --退货时间

--创建销售表
create table T_Sales(
id int IDENTITY(1,1) primary key, --编号
SalesID varchar(50), --销售单号
depotname varchar(50), --仓库名称
Barcode varchar(50), --条形码
GoodsCode varchar(50), --货品编码
GoodsName varchar(200), --货品名称
GoodsUnit varchar(50), --货品规格
units varchar(10), --单位
Number float, --数量
Price float, --单价
Addtime smalldatetime, --销售时间
jingbanren varchar(50), --经办人
zhizuoren varchar(50), --制作人
memo varchar(200)) --备注

--创建销售退货表
create table T_outsales(
id int IDENTITY(1,1) primary key, --编号
OutsalesID varchar(50), --退货单号
depotname varchar(50), --仓库名称
Barcode varchar(50), --条形码
GoodsCode varchar(50), --货品编码
GoodsName varchar(200), --货品名称
GoodsUnit varchar(50), --货品规格
units varchar(10), --单位
Number float, --数量
Price float, --单价
OnBillCode varchar(50), --对应的销售单号
Addtime smalldatetime, --退货时间
jingbanren varchar(50), --经办人
zhizuoren varchar(50), --制作人
memo varchar(200)) --备注


--创建仓库损益表
create table T_sunyi(
id int IDENTITY(1,1) primary key, --编号
SunyiID varchar(50), --损益单号
depotname varchar(50), --仓库名称
Barcode varchar(50), --条形码
GoodsCode varchar(50), --货品编码
GoodsName varchar(200), --货品名称
GoodsUnit varchar(50), --货品规格
units varchar(10), --单位
Number float, --数量
Price float, --单价
Addtime smalldatetime, --损益时间
jingbanren varchar(50), --经办人
yuanyou varchar(500), --原因
memo varchar(200)) --备注


CREATE TABLE PosDetail ( --POS详细单
BillNo [nvarchar] (30) NOT NULL , --//POS单号
OrderID [smallint] NOT NULL , --//序号
GoodsId [nvarchar] (30) NOT NULL , --//货品编号
Quantity [numeric](18, 3) NOT NULL , --//数量
Price [numeric](18, 2) NOT NULL , --//商品单价
Discount [smallint] NOT NULL , --//折扣
Amount [numeric](18, 2) NOT NULL, --//金额
Store [nvarchar] (30) NOT NULL , --//仓库
BillDate [datetime] NOT NULL --//开单日期
)



查询汇总的时候是通过仓库名称和货品编码连接各表

想实现查询汇总成

仓库名称,货品编码,条形码,货品名称,规格,利润,库存单位,库存总数量,采购收货单位,采购总数量,采购总金额,采购退货单位,采购退货总数量,采购退货总金额,销售出货单位,销售退货总数量,采购退货总金额,报损单位,报损总数量,报损总金额,报溢单位,报溢总数量,报溢总金额,POS销售单位,POS销售总数量,POS销售总金额

其中报损是查询仓库损益表T_sunyi数量为负数,即小于0的那些
报溢是查询仓库损益表T_sunyi数量为正数,即大于0的那些

[llq0240]

'----------------填充过滤Grid1涵数---------
Private Sub guolv(name As String, code As String)

Dim rs As New ADODB.Recordset
Dim sql As String
If name = "" Then
If code = "" Then
sql = "select depot.depotname as 仓库名称,depot.GoodsCode as 货品编码,depot.Barcode as 条形码,"
sql = sql & "depot.GoodsName as 货品名称,T_Goods.GoodsUnit as 规格,depot.Number As 数量,"
sql = sql & "T_Goods.Units as 单位 from depot,T_Goods where "
sql = sql & "depot.GoodsCode=T_Goods.GoodsCode"
End If

If code <> "" Then
sql = "select depot.depotname as 仓库名称,depot.GoodsCode as 货品编码,depot.Barcode as 条形码,"
sql = sql & "depot.GoodsName as 货品名称,T_Goods.GoodsUnit as 规格,depot.Number As 数量,"
sql = sql & "T_Goods.Units as 单位 from depot,T_Goods where "
sql = sql & "depot.GoodsCode=T_Goods.GoodsCode and depot.GoodsCode='" & code & "'"
End If
End If

If name <> "" Then
If code = "" Then
sql = "select depot.depotname as 仓库名称,depot.GoodsCode as 货品编码,depot.Barcode as 条形码,"
sql = sql & "depot.GoodsName as 货品名称,T_Goods.GoodsUnit as 规格,depot.Number As 数量,"
sql = sql & "T_Goods.Units as 单位 from depot,T_Goods where "
sql = sql & "depot.GoodsCode=T_Goods.GoodsCode and depot.depotname='" & name & "'"
End If

If code <> "" Then
sql = "select depot.depotname as 仓库名称,depot.GoodsCode as 货品编码,depot.Barcode as 条形码,"
sql = sql & "depot.GoodsName as 货品名称,T_Goods.GoodsUnit as 规格,depot.Number As 数量,"
sql = sql & "T_Goods.Units as 单位 from depot,T_Goods where "
sql = sql & "depot.GoodsCode=T_Goods.GoodsCode and depot.GoodsCode='" & code & "' and depot.depotname='" & name & "'"
End If
End If

rs.Open sql, conn, 1, 1

[llq0240]

If Not rs.EOF Then
Dim i As Integer
Dim str As String
Dim row As Integer
row = 1
Grid1.FixedRows = 2
Grid1.Rows = rs.RecordCount + 2
Grid1.Cols = 30

Grid1.Column(3).Alignment = cellCenterCenter
Grid1.Column(5).Alignment = cellCenterCenter
Grid1.Column(7).Alignment = cellCenterCenter
Grid1.Column(8).Alignment = cellCenterCenter
Grid1.Column(9).Alignment = cellCenterCenter
Grid1.Column(12).Alignment = cellCenterCenter
Grid1.Column(15).Alignment = cellCenterCenter
Grid1.Column(18).Alignment = cellCenterCenter
Grid1.Column(21).Alignment = cellCenterCenter
Grid1.Column(24).Alignment = cellCenterCenter
Grid1.Column(27).Alignment = cellCenterCenter

Grid1.Column(6).FormatString = "##,##0.00"
Grid1.Column(8).FormatString = "##,##0.00"
Grid1.Column(10).FormatString = "##,##0.00"
Grid1.Column(11).FormatString = "##,##0.00"
Grid1.Column(13).FormatString = "##,##0.00"
Grid1.Column(14).FormatString = "##,##0.00"
Grid1.Column(16).FormatString = "##,##0.00"
Grid1.Column(17).FormatString = "##,##0.00"
Grid1.Column(19).FormatString = "##,##0.00"
Grid1.Column(20).FormatString = "##,##0.00"
Grid1.Column(22).FormatString = "##,##0.00"
Grid1.Column(23).FormatString = "##,##0.00"
Grid1.Column(25).FormatString = "##,##0.00"
Grid1.Column(26).FormatString = "##,##0.00"
Grid1.Column(28).FormatString = "##,##0.00"
Grid1.Column(29).FormatString = "##,##0.00"

Grid1.AutoRedraw = True
Grid1.Refresh
Grid1.Range(0, 0, 1, 0).MergeCells = True
Grid1.Range(0, 1, 1, 1).MergeCells = True
Grid1.Range(0, 2, 1, 2).MergeCells = True
Grid1.Range(0, 3, 1, 3).MergeCells = True
Grid1.Range(0, 4, 1, 4).MergeCells = True
Grid1.Range(0, 5, 1, 5).MergeCells = True
Grid1.Range(0, 6, 1, 6).MergeCells = True
Grid1.Range(0, 7, 0, 8).MergeCells = True
Grid1.Range(0, 9, 0, 11).MergeCells = True
Grid1.Range(0, 12, 0, 14).MergeCells = True
Grid1.Range(0, 15, 0, 17).MergeCells = True
Grid1.Range(0, 18, 0, 20).MergeCells = True
Grid1.Range(0, 21, 0, 23).MergeCells = True
Grid1.Range(0, 24, 0, 26).MergeCells = True
Grid1.Range(0, 27, 0, 29).MergeCells = True

Grid1.Cell(0, 1).Text = "仓库名称"
Grid1.Cell(0, 2).Text = "货品编码"
Grid1.Cell(0, 3).Text = "条形码"
Grid1.Cell(0, 4).Text = "货品名称"
Grid1.Cell(0, 5).Text = "规格"
Grid1.Cell(0, 6).Text = "利润"
Grid1.Cell(0, 7).Text = "库存"
Grid1.Cell(0, 9).Text = "采购收货"
Grid1.Cell(0, 12).Text = "采购退货"
Grid1.Cell(0, 15).Text = "销售出货"
Grid1.Cell(0, 18).Text = "销售退货"
Grid1.Cell(0, 21).Text = "报损"
Grid1.Cell(0, 24).Text = "报溢"
Grid1.Cell(0, 27).Text = "POS销售"
Grid1.Cell(1, 7).Text = "单位"
Grid1.Cell(1, 8).Text = "数量"
Grid1.Cell(1, 9).Text = "单位"
Grid1.Cell(1, 10).Text = "数量"
Grid1.Cell(1, 11).Text = "金额"
Grid1.Cell(1, 12).Text = "单位"
Grid1.Cell(1, 13).Text = "数量"
Grid1.Cell(1, 14).Text = "金额"
Grid1.Cell(1, 15).Text = "单位"
Grid1.Cell(1, 16).Text = "数量"
Grid1.Cell(1, 17).Text = "金额"
Grid1.Cell(1, 18).Text = "单位"
Grid1.Cell(1, 19).Text = "数量"
Grid1.Cell(1, 20).Text = "金额"
Grid1.Cell(1, 21).Text = "单位"
Grid1.Cell(1, 22).Text = "数量"
Grid1.Cell(1, 23).Text = "金额"
Grid1.Cell(1, 24).Text = "单位"
Grid1.Cell(1, 25).Text = "数量"
Grid1.Cell(1, 26).Text = "金额"
Grid1.Cell(1, 27).Text = "单位"
Grid1.Cell(1, 28).Text = "数量"
Grid1.Cell(1, 29).Text = "金额"
Grid1.Range(2, 1, 2, Grid1.Cols - 1).Selected

rs.MoveFirst

[llq0240]

Do While Not rs.EOF
row = row + 1
Grid1.Cell(row, 1).Text = rs.Fields(0).value
Grid1.Cell(row, 2).Text = rs.Fields(1).value
Grid1.Cell(row, 3).Text = rs.Fields(2).value
Grid1.Cell(row, 4).Text = rs.Fields(3).value
Grid1.Cell(row, 5).Text = rs.Fields(4).value
Grid1.Cell(row, 7).Text = rs.Fields(6).value
Grid1.Cell(row, 8).Text = rs.Fields(5).value

'----采购入库
Dim sl As Single
Dim je As Single
Dim sql1 As String
Dim rs1 As New ADODB.Recordset
sql1 = "select * from T_Import where depotname='" & Grid1.Cell(row, 1).Text & "' and GoodsCode='" & Grid1.Cell(row, 2).Text & "' and (convert(smalldatetime,Addtime) > '" & DateAdd("d", -1, DTPicker1.value) & "') and (convert(smalldatetime,Addtime) < '" & DateAdd("d", 1, DTPicker2.value) & "')"
rs1.Open sql1, conn, 1, 1
Do While Not rs1.EOF
sl = sl + rs1("Number")
je = je + rs1("Number") * rs1("InPrice")
rs1.MoveNext
Loop
rs1.Close
Set rs1 = Nothing
Grid1.Cell(row, 9).Text = Grid1.Cell(row, 7).Text
Grid1.Cell(row, 10).Text = sl
Grid1.Cell(row, 11).Text = FormatCurrency(je)

'---采购退货
Dim sl1 As Single
Dim je1 As Single
Dim sql2 As String
Dim rs2 As New ADODB.Recordset
sql2 = "select * from T_Outport where depotname='" & Grid1.Cell(row, 1).Text & "' and GoodsCode='" & Grid1.Cell(row, 2).Text & "' and (convert(smalldatetime,Addtime) > '" & DateAdd("d", -1, DTPicker1.value) & "') and (convert(smalldatetime,Addtime) < '" & DateAdd("d", 1, DTPicker2.value) & "')"
rs2.Open sql2, conn, 1, 1
Do While Not rs2.EOF
sl1 = sl1 + rs2("Number")
je1 = je1 + rs2("Number") * rs2("InPrice")
rs2.MoveNext
Loop
rs2.Close
Set rs2 = Nothing
Grid1.Cell(row, 12).Text = Grid1.Cell(row, 7).Text
Grid1.Cell(row, 13).Text = sl1
Grid1.Cell(row, 14).Text = FormatCurrency(je1)
'---销售
Dim sl2 As Single
Dim je2 As Single
Dim sql3 As String
Dim rs3 As New ADODB.Recordset
sql3 = "select * from T_Sales where depotname='" & Grid1.Cell(row, 1).Text & "' and GoodsCode='" & Grid1.Cell(row, 2).Text & "' and (convert(smalldatetime,Addtime) > '" & DateAdd("d", -1, DTPicker1.value) & "') and (convert(smalldatetime,Addtime) < '" & DateAdd("d", 1, DTPicker2.value) & "')"
rs3.Open sql3, conn, 1, 1
Do While Not rs3.EOF
sl2 = sl2 + rs3("Number")
je2 = je2 + rs3("Number") * rs3("Price")
rs3.MoveNext
Loop
rs3.Close
Set rs3 = Nothing
Grid1.Cell(row, 15).Text = Grid1.Cell(row, 7).Text
Grid1.Cell(row, 16).Text = sl2
Grid1.Cell(row, 17).Text = FormatCurrency(je2)

'---销售退货
Dim sl3 As Single
Dim je3 As Single
Dim sql4 As String
Dim rs4 As New ADODB.Recordset
sql4 = "select * from T_outsales where depotname='" & Grid1.Cell(row, 1).Text & "' and GoodsCode='" & Grid1.Cell(row, 2).Text & "' and (convert(smalldatetime,Addtime) > '" & DateAdd("d", -1, DTPicker1.value) & "') and (convert(smalldatetime,Addtime) < '" & DateAdd("d", 1, DTPicker2.value) & "')"
rs4.Open sql4, conn, 1, 1
Do While Not rs4.EOF
sl3 = sl3 + rs4("Number")
je3 = je3 + rs4("Number") * rs4("Price")
rs4.MoveNext
Loop
rs4.Close
Set rs4 = Nothing
Grid1.Cell(row, 18).Text = Grid1.Cell(row, 7).Text
Grid1.Cell(row, 19).Text = sl3
Grid1.Cell(row, 20).Text = FormatCurrency(je3)

'---报损/报溢
Dim sl4 As Single
Dim je4 As Single
Dim sl5 As Single
Dim je5 As Single
Dim sql5 As String
Dim rs5 As New ADODB.Recordset
sql5 = "select * from T_sunyi where depotname='" & Grid1.Cell(row, 1).Text & "' and GoodsCode='" & Grid1.Cell(row, 2).Text & "' and (convert(smalldatetime,Addtime) > '" & DateAdd("d", -1, DTPicker1.value) & "') and (convert(smalldatetime,Addtime) < '" & DateAdd("d", 1, DTPicker2.value) & "')"
rs5.Open sql5, conn, 1, 1
Do While Not rs5.EOF
If rs5("Number") < 0 Then
sl4 = sl4 + rs5("Number")
je4 = je4 + rs5("Number") * rs5("Price")
Else
sl5 = sl5 + rs5("Number")
je5 = je5 + rs5("Number") * rs5("Price")
End If
rs5.MoveNext
Loop
rs5.Close
Set rs5 = Nothing
Grid1.Cell(row, 21).Text = Grid1.Cell(row, 7).Text
Grid1.Cell(row, 22).Text = sl4
Grid1.Cell(row, 23).Text = FormatCurrency(je4)
Grid1.Cell(row, 24).Text = Grid1.Cell(row, 9).Text
Grid1.Cell(row, 25).Text = sl5
Grid1.Cell(row, 26).Text = FormatCurrency(je5)

'---pos销售
Dim sl6 As Single
Dim je6 As Single
Dim sql6 As String
Dim rs6 As New ADODB.Recordset
sql6 = "select sum(Quantity),sum(Amount) from PosDetail where Store='" & Grid1.Cell(row, 1).Text & "' and (convert(smalldatetime,BillDate) > '" & DateAdd("d", -1, DTPicker1.value) & "') and (convert(smalldatetime,BillDate) < '" & DateAdd("d", 1, DTPicker2.value) & "') and GoodsId='" & Grid1.Cell(row, 2).Text & "' group by GoodsId "
rs6.Open sql6, conn, 1, 1
If Not rs6.EOF Then
sl6 = rs6.Fields(0)
je6 = rs6.Fields(1)
End If
rs6.Close
Set rs6 = Nothing
Grid1.Cell(row, 27).Text = Grid1.Cell(row, 7).Text
Grid1.Cell(row, 28).Text = sl6
Grid1.Cell(row, 29).Text = FormatCurrency(je6)


Dim a As Single
'6=17-20+26+29-11+14-23
a = Grid1.Cell(row, 17).Text
a = a - Grid1.Cell(row, 20).Text
a = a + Grid1.Cell(row, 26).Text
a = a + Grid1.Cell(row, 29).Text
a = a - Grid1.Cell(row, 11).Text
a = a + Grid1.Cell(row, 14).Text
a = a - Grid1.Cell(row, 23).Text
Grid1.Cell(row, 6).Text = FormatCurrency(a)
rs.MoveNext
Loop
Grid1.Range(2, 6, Grid1.Rows - 1, 6).ForeColor = vbRed
Grid1.Range(2, 11, Grid1.Rows - 1, 11).ForeColor = vbBlue
Grid1.Range(2, 14, Grid1.Rows - 1, 14).ForeColor = vbBlue
Grid1.Range(2, 17, Grid1.Rows - 1, 17).ForeColor = vbBlue
Grid1.Range(2, 20, Grid1.Rows - 1, 20).ForeColor = vbBlue
Grid1.Range(2, 23, Grid1.Rows - 1, 23).ForeColor = vbBlue
Grid1.Range(2, 26, Grid1.Rows - 1, 26).ForeColor = vbBlue
Grid1.Range(2, 29, Grid1.Rows - 1, 29).ForeColor = vbBlue
Else
Grid1.FixedRows = 1
Grid1.Rows = 2
Grid1.Cols = 2
Grid1.Cell(0, 1).Text = ""
End If

rs.Close
Set rs = Nothing

End Sub