Private Sub Command13_Click() Set CONN = New ADODB.Connection Dim rs As New ADODB.Recordset lj = App.Path mdbfilename = lj & "\T.mdb" CONN.Open "provider=Microsoft.jet.OLEDB.4.0;data source=" & mdbfilename sql = "SELECT 进货表.ID, Sum(进货表.进货数) AS 总进货量, Sum(出货表.出货数) AS 总出货量, [总进货量]-[总出货量] AS 总结存数量" _ & " FROM 进货表 INNER JOIN 出货表 ON 进货表.ID = 出货表.ID GROUP BY 进货表.ID;" rs.Open sql, CONN, adOpenKeyset, adLockReadOnly, adCmdText Set MSHFlexGrid1.DataSource = rs MSHFlexGrid1.Refresh With MSHFlexGrid1 .AllowBigSelection = True ' 设置网格样式 .FillStyle = flexFillRepeat For i = 0 To .Rows - 1 .Row = i: .Col = .FixedCols .ColSel = .Cols() - .FixedCols - 1 If i Mod 2 = 0 Then .CellBackColor = &HC0C0C0 ' 浅灰 End If Next i End With MSHFlexGrid1.Visible = True End Sub
要先统计再运算 SELECT IIF(ISNULL(进货统计.ID),出货统计.ID,进货统计.ID) AS ID, 进货统计.总进货量, IIF(ISNULL(出货统计.总出货量),0,出货统计.总出货量) AS 总出货量, 进货统计.总进货量 - IIF(ISNULL(出货统计.总出货量),0,出货统计.总出货量) AS 总结存数量 FROM (SELECT ID, Sum(进货数) AS 总进货量 FROM 进货表 GROUP BY ID) AS 进货统计 LEFT JOIN (SELECT ID, Sum(出货数) AS 总出货量 FROM 出货表 GROUP BY ID) AS 出货统计 ON 进货统计.ID = 出货统计.ID;
Set CONN = New ADODB.Connection
Dim rs As New ADODB.Recordset
lj = App.Path
mdbfilename = lj & "\T.mdb"
CONN.Open "provider=Microsoft.jet.OLEDB.4.0;data source=" & mdbfilename
sql = "SELECT 进货表.ID, Sum(进货表.进货数) AS 总进货量, Sum(出货表.出货数) AS 总出货量, [总进货量]-[总出货量] AS 总结存数量" _
& " FROM 进货表 INNER JOIN 出货表 ON 进货表.ID = 出货表.ID GROUP BY 进货表.ID;"
rs.Open sql, CONN, adOpenKeyset, adLockReadOnly, adCmdText
Set MSHFlexGrid1.DataSource = rs
MSHFlexGrid1.Refresh
With MSHFlexGrid1
.AllowBigSelection = True ' 设置网格样式
.FillStyle = flexFillRepeat
For i = 0 To .Rows - 1
.Row = i: .Col = .FixedCols
.ColSel = .Cols() - .FixedCols - 1
If i Mod 2 = 0 Then
.CellBackColor = &HC0C0C0 ' 浅灰
End If
Next i
End With
MSHFlexGrid1.Visible = True
End Sub
SELECT IIF(ISNULL(进货统计.ID),出货统计.ID,进货统计.ID) AS ID,
进货统计.总进货量,
IIF(ISNULL(出货统计.总出货量),0,出货统计.总出货量) AS 总出货量,
进货统计.总进货量 - IIF(ISNULL(出货统计.总出货量),0,出货统计.总出货量) AS 总结存数量
FROM (SELECT ID, Sum(进货数) AS 总进货量 FROM 进货表 GROUP BY ID) AS 进货统计
LEFT JOIN (SELECT ID, Sum(出货数) AS 总出货量 FROM 出货表 GROUP BY ID) AS 出货统计
ON 进货统计.ID = 出货统计.ID;