我的這個小程式非常簡單，資料庫2個表，1個進貨表，1個出貨表，2個表的主鍵都是貨號（ID )查詢日期時間段中賣了什么，查詢哪個貨號還有多少庫存就行了！感謝啊
uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
要先統計再運算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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/155977.html
上一篇：列印報表位置移動