Private Sub CommandButton1_Click()
Dim lsSQL As String: Dim rst As New ADODB.Recordset
On Error Resume Next
If cnn.State <> 1 Then Moketnoi
cnn.Execute "Drop table tblDMSD"
'Tao bang tblDMSD de chua danh muc DMSD
lsSQL = "SELECT DMXUAT.NGAY, DMXUAT.PHIEU, DMXUAT.MAHANG, DMHANG.TENHANG, DINHMUC.MANL, DMVATTU.TENNL, DINHMUC.DMVT, DMXUAT.XUAT, [DMVT]*[XUAT] AS TONGDM INTO tblDMSD " & _
"FROM DMVATTU INNER JOIN ((DMHANG INNER JOIN DINHMUC ON DMHANG.MAHANG = DINHMUC.MAHANG) INNER JOIN DMXUAT ON DMHANG.MAHANG = DMXUAT.MAHANG) ON DMVATTU.MANL = DINHMUC.MANL;"
rst.Open lsSQL, cnn, adOpenStatic, adLockReadOnly
'Tao bang tblTongDM de chua tong hop DMSD
lsSQL = "SELECT tblDMSD.MANL, tblDMSD.TENNL, Sum(tblDMSD.TONGDM) AS Tong INTO tblTongDM " & _
"FROM tblDMSD " & _
"GROUP BY tblDMSD.MANL, tblDMSD.TENNL;"
rst.Open lsSQL, cnn, adOpenStatic, adLockReadOnly
'Tao bang tblTongNhap de chua hang nhap
lsSQL = "SELECT DMNHAP.MANL, Sum(DMNHAP.NHAP) AS TongNhap INTO tblTongNhap " & _
"FROM DMNHAP " & _
"GROUP BY DMNHAP.MANL;"
'Tien hang truy van va tinh so luong ton kho
rst.Open lsSQL, cnn, adOpenStatic, adLockReadOnly
lsSQL = "SELECT DMVATTU.MANL, DMVATTU.TENNL, DMVATTU.DVT, tblTongNhap.TongNhap, tblTongDM.Tong AS TongXuat, IIf(IsNull([TongNhap]),0,[TongNhap])-IIf(IsNull([tong]),0,[tong]) AS Ton, DMVATTU.DONGIA, [DONGIA]*[ton] AS ThanhTien " & _
"FROM (tblTongDM RIGHT JOIN DMVATTU ON tblTongDM.MANL = DMVATTU.MANL) LEFT JOIN tblTongNhap ON DMVATTU.MANL = tblTongNhap.MANL;"
rst.Open lsSQL, cnn, adOpenStatic, adLockReadOnly
'Dua du lieu co duoc ra sheet TONKHO
Sheets("TONKHO").Range("A5").CopyFromRecordset rst
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing
End Sub
