Truy vấn trong SQL? (1 người xem)

[quanluu]

Xin chào quanluu1989,
Cảm ơn Bạn đã góp ý, đúng là khi lấy một lượng dữ liệu lớn từ máy chủ về thì nên thực hiện trực tiếp qua Sever ạ.
Vba trong trường hợp này vẫn có thể sử dụng được ạ,nếu dữ liệu không quá nhiều(khoảng hai trăm nghìn dòng trở xuống- do số lượng bản ghi đã khống chế từ câu lệnh lấy dữ liệu từ mày chủ).
Nhưng để đảm bảo có thể tổng hợp dữ liệu ở một khoảng thời gian dài thì việc sử dụng vba có thể sẽ bị treo do dữ liệu lấy về quá nhiều ạ.
Với ví dụ trên OT chưa hiểu và nắm bắt được phiền bạn giúp đỡ OT làm viết câu lệnh select theo tất cả điều kiện tín toán trong cột "TONG CONG" được không ạ.
Cảm ơn Bạn nhiều.

Vấn đề không hẳn là dữ liệu lớn vì dùng procedure chưa chắc nhanh hơn, mà quan trọng ở bảo mật, vì người dùng biết bạn đang truy vấn bảng nào, nếu người ta có quyền write thì người ta có thể làm nhiều việc xấu.

Còn query như sau:
SELECT MAHANG,TENHANG,CHUNGLOAI , sum(TONGCONG) AS TONGCONG
FROM TABLE
WHERE NGAY BETWEEN TU_NGAY AND DEN_NGAY
GROUP BY MAHANG,TENHANG,CHUNGLOAI

 

[Hai Lúa Miền Tây]

Xin chào các Bạn,

Nhờ các Bạn giúp đỡ OT sử dụng ADO/SQL để thực hiện trường hợp sau với ạ:
Từ bảng "DULIEU" lọc duy nhất 3 cột MAHANG,TENHANG,CHUNGLOAI,sau đó tính tổng số lượng theo vùng công thức màu hồng tím dựa vào điều kiện từ ngày đến ngày. Kết quả mong như bảng "TH_MH" ạ.
OT sử dụng câu lệnh "SELECT DISTINCT MAHANG,TENHANG,CHUNGLOAI" thì lấy được dữ liệu duy nhất của 3 trường, nhưng chưa biết cách tính toán cho vùng công thức màu hồng ạ.


View attachment 236068

View attachment 236069

Bạn thử code sau nhé:

Sub TongHopDL_HLMT()
    Dim strDkNgay As String, strSQL As String, strLine As String, strGroup As String
    strDkNgay = " NGAYTHANG BETWEEN #2020-03-26# AND #2020-04-13# "
    strGroup = " MAHANG,TENHANG,CHUNGLOAI "
    strLine = " Val(iif(isnull(LINE_01),0,LINE_01)) as LINE_01,Val(iif(isnull(LINE_02),0,LINE_02)) as LINE_02,Val(iif(isnull(LINE_03),0,LINE_03)) as LINE_03,Val(iif(isnull(LINE_04) ,0,LINE_04))  as LINE_04 " & _
              ",Val(iif(isnull(LINE_05),0,LINE_05)) as LINE_05,Val(iif(isnull(LINE_06),0,LINE_06)) as LINE_06,Val(iif(isnull(LINE_07),0,LINE_07)) as  LINE_07,Val(iif(isnull(LINE_08),0,LINE_08)) as LINE_08 "
    strSQL = "Select " & strGroup & ",0 as SOLUONG,0 AS SL_KT,0 AS SL_LB," & strLine & " From [DULIEU$] WHERE " & strDkNgay & " AND CONGDOAN LIKE '%CD%' AND PHANLOAI LIKE '%LB%'"
    strSQL = strSQL & " Union all Select " & strGroup & ",SOLUONG ,0,0,0,0,0,0,0,0,0,0 From [DULIEU$] WHERE " & strDkNgay & " AND CONGDOAN LIKE '%CD%'"
    strSQL = strSQL & " Union all Select " & strGroup & ",0,SOLUONG,0,0,0,0,0,0,0,0,0 From [DULIEU$] WHERE " & strDkNgay & " AND CONGDOAN LIKE '%CD%' AND PHANLOAI LIKE 'KHXX001'"
    strSQL = strSQL & " Union all Select " & strGroup & ",0,0,SOLUONG,0,0,0,0,0,0,0,0 From [DULIEU$] WHERE " & strDkNgay & " AND CONGDOAN LIKE '%CD%' AND PHANLOAI LIKE '%LB%'"
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 12.0")
        Sheet1.Range("A2").CopyFromRecordset .Execute("Select " & strGroup & ",Sum(SoLuong),Sum(SL_KT),Sum(SL_LB),Sum(SL_LB)/Sum(SoLuong),Sum(LINE_01),Sum(LINE_02),Sum(LINE_03),Sum(LINE_04),Sum(LINE_05),Sum(LINE_06),Sum(LINE_07),Sum(LINE_08) From ( " & strSQL & ") Group by " & strGroup)
    End With
End Sub

 

[quanluu]

Bạn thử code sau nhé:

Sub TongHopDL_HLMT()
    Dim strDkNgay As String, strSQL As String, strLine As String, strGroup As String
    strDkNgay = " NGAYTHANG BETWEEN #2020-03-26# AND #2020-04-13# "
    strGroup = " MAHANG,TENHANG,CHUNGLOAI "
    strLine = " Val(iif(isnull(LINE_01),0,LINE_01)) as LINE_01,Val(iif(isnull(LINE_02),0,LINE_02)) as LINE_02,Val(iif(isnull(LINE_03),0,LINE_03)) as LINE_03,Val(iif(isnull(LINE_04) ,0,LINE_04))  as LINE_04 " & _
              ",Val(iif(isnull(LINE_05),0,LINE_05)) as LINE_05,Val(iif(isnull(LINE_06),0,LINE_06)) as LINE_06,Val(iif(isnull(LINE_07),0,LINE_07)) as  LINE_07,Val(iif(isnull(LINE_08),0,LINE_08)) as LINE_08 "
    strSQL = "Select " & strGroup & ",0 as SOLUONG,0 AS SL_KT,0 AS SL_LB," & strLine & " From [DULIEU$] WHERE " & strDkNgay & " AND CONGDOAN LIKE '%CD%' AND PHANLOAI LIKE '%LB%'"
    strSQL = strSQL & " Union all Select " & strGroup & ",SOLUONG ,0,0,0,0,0,0,0,0,0,0 From [DULIEU$] WHERE " & strDkNgay & " AND CONGDOAN LIKE '%CD%'"
    strSQL = strSQL & " Union all Select " & strGroup & ",0,SOLUONG,0,0,0,0,0,0,0,0,0 From [DULIEU$] WHERE " & strDkNgay & " AND CONGDOAN LIKE '%CD%' AND PHANLOAI LIKE 'KHXX001'"
    strSQL = strSQL & " Union all Select " & strGroup & ",0,0,SOLUONG,0,0,0,0,0,0,0,0 From [DULIEU$] WHERE " & strDkNgay & " AND CONGDOAN LIKE '%CD%' AND PHANLOAI LIKE '%LB%'"
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 12.0")
        Sheet1.Range("A2").CopyFromRecordset .Execute("Select " & strGroup & ",Sum(SoLuong),Sum(SL_KT),Sum(SL_LB),Sum(SL_LB)/Sum(SoLuong),Sum(LINE_01),Sum(LINE_02),Sum(LINE_03),Sum(LINE_04),Sum(LINE_05),Sum(LINE_06),Sum(LINE_07),Sum(LINE_08) From ( " & strSQL & ") Group by " & strGroup)
    End With
End Sub

Sao không dùng OR anh ơi strDkNgay & " AND CONGDOAN LIKE '%CD%' AND (PHANLOAI LIKE '%LB%'"OR PHANLOAI LIKE 'KHXX001'")

 

[Hoàng Nhật Phương]

Bạn thử code sau nhé:

Sub TongHopDL_HLMT()
    Dim strDkNgay As String, strSQL As String, strLine As String, strGroup As String
    strDkNgay = " NGAYTHANG BETWEEN #2020-03-26# AND #2020-04-13# "
    strGroup = " MAHANG,TENHANG,CHUNGLOAI "
    strLine = " Val(iif(isnull(LINE_01),0,LINE_01)) as LINE_01,Val(iif(isnull(LINE_02),0,LINE_02)) as LINE_02,Val(iif(isnull(LINE_03),0,LINE_03)) as LINE_03,Val(iif(isnull(LINE_04) ,0,LINE_04))  as LINE_04 " & _
              ",Val(iif(isnull(LINE_05),0,LINE_05)) as LINE_05,Val(iif(isnull(LINE_06),0,LINE_06)) as LINE_06,Val(iif(isnull(LINE_07),0,LINE_07)) as  LINE_07,Val(iif(isnull(LINE_08),0,LINE_08)) as LINE_08 "
    strSQL = "Select " & strGroup & ",0 as SOLUONG,0 AS SL_KT,0 AS SL_LB," & strLine & " From [DULIEU$] WHERE " & strDkNgay & " AND CONGDOAN LIKE '%CD%' AND PHANLOAI LIKE '%LB%'"
    strSQL = strSQL & " Union all Select " & strGroup & ",SOLUONG ,0,0,0,0,0,0,0,0,0,0 From [DULIEU$] WHERE " & strDkNgay & " AND CONGDOAN LIKE '%CD%'"
    strSQL = strSQL & " Union all Select " & strGroup & ",0,SOLUONG,0,0,0,0,0,0,0,0,0 From [DULIEU$] WHERE " & strDkNgay & " AND CONGDOAN LIKE '%CD%' AND PHANLOAI LIKE 'KHXX001'"
    strSQL = strSQL & " Union all Select " & strGroup & ",0,0,SOLUONG,0,0,0,0,0,0,0,0 From [DULIEU$] WHERE " & strDkNgay & " AND CONGDOAN LIKE '%CD%' AND PHANLOAI LIKE '%LB%'"
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 12.0")
        Sheet1.Range("A2").CopyFromRecordset .Execute("Select " & strGroup & ",Sum(SoLuong),Sum(SL_KT),Sum(SL_LB),Sum(SL_LB)/Sum(SoLuong),Sum(LINE_01),Sum(LINE_02),Sum(LINE_03),Sum(LINE_04),Sum(LINE_05),Sum(LINE_06),Sum(LINE_07),Sum(LINE_08) From ( " & strSQL & ") Group by " & strGroup)
    End With
End Sub

Vấn đề không hẳn là dữ liệu lớn vì dùng procedure chưa chắc nhanh hơn, mà quan trọng ở bảo mật, vì người dùng biết bạn đang truy vấn bảng nào, nếu người ta có quyền write thì người ta có thể làm nhiều việc xấu.

Còn query như sau:
SELECT MAHANG,TENHANG,CHUNGLOAI , sum(TONGCONG) AS TONGCONG
FROM TABLE
WHERE NGAY BETWEEN TU_NGAY AND DEN_NGAY
GROUP BY MAHANG,TENHANG,CHUNGLOAI

OT cảm ơn anh Hai Lúa Miền Tây & quanluu1989,
Hiện code của anh Hai Lúa OT đã test trên Excel OK rồi ạ, còn code của Bạn quanluu1989 OT chưa có điều kiện để làm thử trong SQL Server, khi có kết quả OT sẽ thông tin lại ạ.
Kính chúc GPE nhà mình đầu tháng vui vẻ & thành công.

 

[Hai Lúa Miền Tây]

Sao không dùng OR anh ơi strDkNgay & " AND CONGDOAN LIKE '%CD%' AND (PHANLOAI LIKE '%LB%'"OR PHANLOAI LIKE 'KHXX001'")

Dựa vào file của chủ thớt rồi làm theo từng đoạn thôi bạn. Ý tưởng là thế, còn sử dụng phải tùy biến.

 

[Hoàng Nhật Phương]

Xin chào tất cả mọi người,
Hiện OT đang có một đoạn code bên dưới để lấy dữ liệu từ bảng "Orders" từ SQL đưa về Excel tại Worksheets("Sheet1").Range("A2:Z500")
OT muốn lấy thêm một bảng nữa cùng Database với "Orders" ví dụ "Produtions" , thì câu lệnh là "SELECT * FROM Produtions" để đưa vào Worksheets("Sheet1").Range("AA2:AH5000")

Xin mọi trợ giúp OT làm thế nào để có thể chạy 2 câu lệnh truy vấn này chỉ một lần chạy kết nối, nghĩa là đưa cả 2 bảng "Orders" và "Produtions"
Vào 2 vùng dữ liệu khác nhau khi chạy Sub ADOExcelSQLServer?

Sub ADOExcelSQLServer()

    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    Server_Name = "Server_Name" ' Enter your server name here
    Database_Name = "DB_TEST" ' Enter your  database name here
    User_ID = "Sa" ' enter your user ID here
    Password = "246357" ' Enter your password here
    
    SQLStr = "SELECT * FROM Orders" ' Enter your SQL here

    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    rs.Open SQLStr, Cn, adOpenStatic

    With Worksheets("Sheet1").Range("A2:Z500")
        .ClearContents
        .CopyFromRecordset rs
    End With

    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

 

[ongke0711]

Xin chào tất cả mọi người,
Hiện OT đang có một đoạn code bên dưới để lấy dữ liệu từ bảng "Orders" từ SQL đưa về Excel tại Worksheets("Sheet1").Range("A2:Z500")
OT muốn lấy thêm một bảng nữa cùng Database với "Orders" ví dụ "Produtions" , thì câu lệnh là "SELECT * FROM Produtions" để đưa vào Worksheets("Sheet1").Range("AA2:AH5000")

Xin mọi trợ giúp OT làm thế nào để có thể chạy 2 câu lệnh truy vấn này chỉ một lần chạy kết nối, nghĩa là đưa cả 2 bảng "Orders" và "Produtions"
Vào 2 vùng dữ liệu khác nhau khi chạy Sub ADOExcelSQLServer?

Em lặp lại việc gán câu lệnh SQL cho recordset mới trước khi đóng cn.

Sub ADOExcelSQLServer()

    ...
  
    SQLStr = "SELECT * FROM Orders" ' Enter your SQL here

    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    rs.Open SQLStr, Cn, adOpenStatic

    With Worksheets("Sheet1").Range("A2:Z500")
        .ClearContents
        .CopyFromRecordset rs
    End With

'Thêm đoạn này.
SQLStr = "SELECT * FROM Production"
rs.Open SQLStr, Cn, adOpenStatic

    With Worksheets("Sheet1").Range("AA2:AH5000")
        .ClearContents
        .CopyFromRecordset rs
    End With


    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

 

[ppc0312]

Xin chào tất cả mọi người,
Hiện OT đang có một đoạn code bên dưới để lấy dữ liệu từ bảng "Orders" từ SQL đưa về Excel tại Worksheets("Sheet1").Range("A2:Z500")
OT muốn lấy thêm một bảng nữa cùng Database với "Orders" ví dụ "Produtions" , thì câu lệnh là "SELECT * FROM Produtions" để đưa vào Worksheets("Sheet1").Range("AA2:AH5000")

Xin mọi trợ giúp OT làm thế nào để có thể chạy 2 câu lệnh truy vấn này chỉ một lần chạy kết nối, nghĩa là đưa cả 2 bảng "Orders" và "Produtions"
Vào 2 vùng dữ liệu khác nhau khi chạy Sub ADOExcelSQLServer?

Sub ADOExcelSQLServer()

    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    Server_Name = "Server_Name" ' Enter your server name here
    Database_Name = "DB_TEST" ' Enter your  database name here
    User_ID = "Sa" ' enter your user ID here
    Password = "246357" ' Enter your password here
  
    SQLStr = "SELECT * FROM Orders" ' Enter your SQL here

    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    rs.Open SQLStr, Cn, adOpenStatic

    With Worksheets("Sheet1").Range("A2:Z500")
        .ClearContents
        .CopyFromRecordset rs
    End With

    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

Có vẻ bạn cần tìm hiểu rõ SQL và ADO thêm chút là biến tấu được, khỏi nhờ diễn đàn chi cho vất vả

 

[Hoàng Nhật Phương]

Em lặp lại việc gán câu lệnh SQL cho recordset mới trước khi đóng cn.

Sub ADOExcelSQLServer()

    ...
 
    SQLStr = "SELECT * FROM Orders" ' Enter your SQL here

    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    rs.Open SQLStr, Cn, adOpenStatic

    With Worksheets("Sheet1").Range("A2:Z500")
        .ClearContents
        .CopyFromRecordset rs
    End With

'Thêm đoạn này.
SQLStr = "SELECT * FROM Production"
rs.Open SQLStr, Cn, adOpenStatic

    With Worksheets("Sheet1").Range("AA2:AH5000")
        .ClearContents
        .CopyFromRecordset rs
    End With


    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

Xin chào ongke0711,
Đúng rồi Anh rồi anh, vậy mà OT không nghĩ ra nên cứ để việc kết nối đóng & lặp lại nhiều lần.
Cảm ơn Anh nhiều ạ.

Có vẻ bạn cần tìm hiểu rõ SQL và ADO thêm chút là biến tấu được, khỏi nhờ diễn đàn chi cho vất vả

Cảm ơn Bạn, không có diễn đàn không có các Thầy/Bác/Anh/Chị và các Bạn ở đây là mình chết chắc đó

 

[ongke0711]

Đúng rồi Anh rồi anh, vậy mà OT không nghĩ ra nên cứ để việc kết nối đóng & lặp lại nhiều lần.

Đây là lý do trong mấy file làm demo lúc trước, anh luôn có hàm tạo kết nối (connection) riêng và hàm lấy Recordset riêng chứ không gộp chung vô cái hàm như của em hiện tại.
- Khi khởi chạy sẽ gọi hàm kết nối rồi để đó.
- Lấy recordset các kiểu, sau khi xong mới đóng kết nối.
Với hàm kết nối độc lập, trong vài trường hợp khi xử lý recordset cần ngắt kết nối tạm thời thì vẫn có thể chủ động ngắt rồi tạo lại sau khi xử lý xong Recordset, cập nhật về hệ thống.

 

[Hoàng Nhật Phương]

Xin chào các Bạn,
Hiện OT đang sử dụng câu lệnh truy vấn lấy dữ liệu trong SQL từ ngày đến ngày thì được dữ liệu như bảng 1 (các mã hàng thể hiện chi tiết theo từng ngày).
Giờ OT mong muốn cũng câu lệnh truy vấn từ ngày đến ngày này nhưng số lượng cho từng mã hàng được thể hiện theo từng tháng (cộng gộp các ngày trong tháng) như bảng 2.
Thì câu lệnh truy vấn được viết thế nào ạ?

 

[Hai Lúa Miền Tây]

Xin chào các Bạn,
Hiện OT đang sử dụng câu lệnh truy vấn lấy dữ liệu trong SQL từ ngày đến ngày thì được dữ liệu như bảng 1 (các mã hàng thể hiện chi tiết theo từng ngày).
Giờ OT mong muốn cũng câu lệnh truy vấn từ ngày đến ngày này nhưng số lượng cho từng mã hàng được thể hiện theo từng tháng (cộng gộp các ngày trong tháng) như bảng 2.
Thì câu lệnh truy vấn được viết thế nào ạ?

Em dùng hàm Format, Code sau chưa đưa điều kiện lọc, em tự thêm vào nhé.

Sub TongHopDL_HLMT()
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 12.0")
        Sheet1.Range("M6").CopyFromRecordset .Execute("Select [CODE],format([DATE],'mm-yyyy'),SUM(QTY) from [Sheet1$A5:C15000] Group by [CODE],format([DATE],'mm-yyyy')")
        
    End With
End Sub

 

[Hoàng Nhật Phương]

Em dùng hàm Format, Code sau chưa đưa điều kiện lọc, em tự thêm vào nhé.

Sub TongHopDL_HLMT()
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 12.0")
        Sheet1.Range("M6").CopyFromRecordset .Execute("Select [CODE],format([DATE],'mm-yyyy'),SUM(QTY) from [Sheet1$A5:C15000] Group by [CODE],format([DATE],'mm-yyyy')")
       
    End With
End Sub

Cảm ơn anh Hai Lúa nhiều ạ, câu lệnh đúng ý OT ạ.
OT đang tìm hiểu câu lệnh SELECT SUM..GROUP BY.. nhưng cách của anh đơn giản hơn & dễ ứng dụng hơn ạ.

 

[Hoàng Nhật Phương]

Xin chào các Bạn,
Các Bạn cho OT hỏi có cách nào thiết lập trong SQL-Database để truy vấn từ ứng dụng Excel đến nó chỉ sử dụng được duy nhất câu lệnh Select không thôi?

 

[Hai Lúa Miền Tây]

Xin chào các Bạn,
Các Bạn cho OT hỏi có cách nào thiết lập trong SQL-Database để truy vấn từ ứng dụng Excel đến nó chỉ sử dụng được duy nhất câu lệnh Select không thôi?

Em nên tạo các Store Procedure, View rồi sau đó gọi nó thôi.

 

[Hoàng Nhật Phương]

Em nên tạo các Store Procedure, View rồi sau đó gọi nó thôi.

Xin chào anh Hai Lúa,
Cảm ơn anh đã gợi ý cho OT.
Có phải OT hiểu như thế này không ạ, nghĩa là không có cách nào khác khi truy vấn trực tiếp đến bảng gốc
mà phải tạo một bản sao cho bảng đó trong SQL và từ Excel sẽ gọi bảng bản sao này phải không anh?

 

[Hai Lúa Miền Tây]

Xin chào anh Hai Lúa,
Cảm ơn anh đã gợi ý cho OT.
Có phải OT hiểu như thế này không ạ, nghĩa là không có cách nào khác khi truy vấn trực tiếp đến bảng gốc
mà phải tạo một bản sao cho bảng đó trong SQL và từ Excel sẽ gọi bảng bản sao này phải không anh?

Hiểu nôm na Store Procedure giống như 1 hàm, ví dụ như hàm trong Excel. Khi cần tìm hay lấy dữ liệu ra bảng khác thì bạn phải vào bảng gốc để tìm, lọc... rồi lấy kết quả đưa chỗ khác. Nhưng bạn dùng hàm thì không phải thao tác trực tiếp trên bảng gốc. Hàm sẽ lấy dữ liệu thảo điều kiện.
Quay lại Store Procedure hơn chỗ là nó có thể thực hiệc các lệnh thêm, sửa, xóa vào dữ liệu bảng gốc.

 

[ongke0711]

Các Bạn cho OT hỏi có cách nào thiết lập trong SQL-Database để truy vấn từ ứng dụng Excel đến nó chỉ sử dụng được duy nhất câu lệnh Select không thôi?

Nếu em có quyền trong SQL Server Management Stufio thì em vô folder Security -> Login -> Tạo user và thiết lập "User Mapping" tới cái Database mà em muốn chỉ cho dùng "SELECT.." là: db_datareader

 

[Hoàng Nhật Phương]

Hiểu nôm na Store Procedure giống như 1 hàm, ví dụ như hàm trong Excel. Khi cần tìm hay lấy dữ liệu ra bảng khác thì bạn phải vào bảng gốc để tìm, lọc... rồi lấy kết quả đưa chỗ khác. Nhưng bạn dùng hàm thì không phải thao tác trực tiếp trên bảng gốc. Hàm sẽ lấy dữ liệu thảo điều kiện.
Quay lại Store Procedure hơn chỗ là nó có thể thực hiệc các lệnh thêm, sửa, xóa vào dữ liệu bảng gốc.

Cảm ơn anh Hai Lúa
OT sẽ tìm hiểu thêm về Store Procedure ạ.
Vấn đề của OT là làm sao để ko cho phép từ ứng dụng Excel ngoài sử dụng câu lệnh select ra thì không được sử dụng câu lệnh khác ấy ạ.
Ví dụ: Từ Excel OT đang sử dụng câu lệnh "Select * from ODER" nếu OT sử dụng câu lệnh khác Select thì sẽ không được ạ.
Nếu sửa code trong Excel thì có thể được nhưng đã là code trong Excel rồi thì vẫn có thể bỏ đi được nếu người nào hiểu code.
OT muốn hỏi cách thiết lập trong SQL để người dùng có sửa code trong Excel thế nào thế nào đi nữa thì cũng chỉ sử dụng được câu lệnh Select thôi ạ.
Không biết là có thể khắc phục được vde này không anh vì nó liên quan đến vấn đề bảo mật anh ạ.
Cảm ơn anh Hai Lúa

Bài đã được tự động gộp: 5/6/20

Nếu em có quyền trong SQL Server Management Stufio thì em vô folder Security -> Login -> Tạo user và thiết lập "User Mapping" tới cái Database mà em muốn chỉ cho dùng "SELECT.." là: db_datareader

Xin chào anh ongke0711,
Cảm ơn anh đã quan tâm và giúp đỡ,hình như đúng là cái này rồi ạ.
Phiền anh ongke0711 có thể hướng dẫn OT chi tiết một chút được không ạ, OT được phép đụng đến SQL mới đầu cảm thấy hứng thú nhưng sau khi biết thêm một chút thì lại thấy nó cũng nguy hiểm nên thấy lo lắng anh ạ.

 

[ongke0711]

Cảm ơn anh đã quan tâm và giúp đỡ,hình như đúng là cái này rồi ạ.
Phiền anh ongke0711 có thể hướng dẫn OT chi tiết một chút được không ạ, OT được phép đụng đến SQL mới đầu cảm thấy hứng thú nhưng sau khi biết thêm một chút thì lại thấy nó cũng nguy hiểm nên thấy lo lắng anh ạ.

Em tạo User Login rồi test xem User đó hoạt động như thế nào, không ảnh hưởng gì đâu. Tạo một Database tạm rồi thử nghiệm trên database đó thôi. Trong hình cái database "abc" là database tạm.

 

[Hoàng Nhật Phương]

Em tạo User Login rồi test xem User đó hoạt động như thế nào, không ảnh hưởng gì đâu. Tạo một Database tạm rồi thử nghiệm trên database đó thôi. Trong hình cái database "abc" là database tạm.

View attachment 238774 View attachment 238775


View attachment 238776

View attachment 238777

Xin cảm ơn ongke0711 nhiều ạ,
Hướng dẫn bằng hình anh thế này rất dễ hiểu.
Muốn chỉ cho dùng "SELECT.." là: chỉ cần chọn "db_datareader" ạ Anh?
vậy còn các db_ còn lại là những gì vậy Anh?

 

[ongke0711]

Xin cảm ơn ongke0711 nhiều ạ,
Hướng dẫn bằng hình anh thế này rất dễ hiểu.
Muốn chỉ cho dùng "SELECT.." là: chỉ cần chọn "db_datareader" ạ Anh?
vậy còn các db_ còn lại là những gì vậy Anh?

Em kiếm tài liệu đọc thêm nhe. Cái vụ phân quyền của SQL Server nó rộng lắm, anh không hướng dẫn nỗi đâu..

 

[Hoàng Nhật Phương]

Em kiếm tài liệu đọc thêm nhe. Cái vụ phân quyền của SQL Server nó rộng lắm, anh không hướng dẫn nỗi đâu..

Xin chào ongke0711,
reader này chắc là đúng rồi theo OT hiểu chỉ là dạng readonly chỉ đọc không ghi
OT sẽ thử ạ, cảm ơn anh nhiều.

 

[VetMini]

Stored Procedure (1) chứ không phải Store Procedure (2).

(1) Tiếng Anh, trạng thái past participle được dùng cho tình huống thụ động (được/bị). Ở đây nói cái procedure (phương thức) được stored (lưu trữ/chứa) sẵn.

(2) cũng trong tiếng Anh, trường hợp 2 này thì Store là danh tự được dùng như tĩnh để bổ nghĩa cho danh tự đi tiếp theo. Và vì hai tự được hiểu theo danh cho nên từ thứ nhất là cửa hàng (kho), và từ thứ hai là quy trình. Dịch hai từ là quy trình cửa hàng.

 

[ongke0711]

Stored Procedure (1) chứ không phải Store Procedure (2).

(2) cũng trong tiếng Anh, trường hợp 2 này thì Store là danh tự được dùng như tĩnh để bổ nghĩa cho danh tự đi tiếp theo. Và vì hai tự được hiểu theo danh cho nên từ thứ nhất là cửa hàng (kho), và từ thứ hai là quy trình. Dịch hai từ là quy trình cửa hàng.

Từ "thủ tục thường trú" biến thành "qui trình cửa hàng" chỉ vì 1 ký tự.

 

[Hoàng Nhật Phương]

Em tạo User Login rồi test xem User đó hoạt động như thế nào, không ảnh hưởng gì đâu. Tạo một Database tạm rồi thử nghiệm trên database đó thôi. Trong hình cái database "abc" là database tạm.

View attachment 238774 View attachment 238775


View attachment 238776

View attachment 238777

Xin chào ongke0711,
Theo hướng dẫn của Anh, OT đã tạo được một UserName đăng nhập mới và để quyền cho các DB là "db_datareader " giờ công việc còn lại là test
Anh có thể chỉ cho OT câu lệnh thêm mới 1 dòng dữ liệu hoặc chỉnh sửa / xóa một dòng dữ liệu bất kỳ trong bảng ODER này được không ạ.
Chỉ cần câu lệnh viết thẳng trong SQL luôn không cần phải qua Excel đâu ạ.
Cảm ơn Anh nhiều ạ.

 

[Hoàng Nhật Phương]

Xin chào ongke0711,
Theo hướng dẫn của Anh, OT đã tạo được một UserName đăng nhập mới và để quyền cho các DB là "db_datareader " giờ công việc còn lại là test
Anh có thể chỉ cho OT câu lệnh thêm mới 1 dòng dữ liệu hoặc chỉnh sửa / xóa một dòng dữ liệu bất kỳ trong bảng ODER này được không ạ.
Chỉ cần câu lệnh viết thẳng trong SQL luôn không cần phải qua Excel đâu ạ.
Cảm ơn Anh nhiều ạ.
View attachment 238786

A! OT thấy rồi những câu lệnh này tìm kiếm là dễ thấy hihi..

 

[ongke0711]

Anh có thể chỉ cho OT câu lệnh thêm mới 1 dòng dữ liệu hoặc chỉnh sửa / xóa một dòng dữ liệu bất kỳ trong bảng ODER này được không ạ.
Chỉ cần câu lệnh viết thẳng trong SQL luôn không cần phải qua Excel đâu ạ.
Cảm ơn Anh nhiều ạ.
View attachment 238786

Thao tác ngay trong SSMS, tạo New query rồi gõ câu lệnh nay vô:

Insert Into dbo.tblHangHoa (STT,MA_HANG,SOLUONG) Values (4,'MH0006',100)

Delete From dbo.tblHangHoa Where STT=3

 

[tuandny1]

Các bác cho em hỏi làm cách nào để biết số dòng chứa SQL của mình ? Cám ơn các bác

 

[VetMini]

Định nghĩa thế nào là một dòng?

 

[Hoàng Nhật Phương]

Thao tác ngay trong SSMS, tạo New query rồi gõ câu lệnh nay vô:

Insert Into dbo.tblHangHoa (STT,MA_HANG,SOLUONG) Values (4,'MH0006',100)

Delete From dbo.tblHangHoa Where STT=3

Xin chào ongke0711,
OT thử đăng nhập với User vừa mới tạo và thử với câu lệnh sau:
UPDATE [OT_TEST].[dbo].[ODER] SET MA_HANG= 'MH1102' WHERE STT=2;
Hệ thống báo:
The UPDATE permission was denied on the object 'ODER', database 'OT_TEST', schema 'dbo'.
Như vậy là OK rồi phải không Anh )

 

[VetMini]

Thao tác ngay trong SSMS, tạo New query rồi gõ câu lệnh nay vô:

Insert Into dbo.tblHangHoa (STT,MA_HANG,SOLUONG) Values (4,'MH0006',100)

Delete From dbo.tblHangHoa Where STT=3

Trời đất, STT (?)
Còn gì là CSDL LH nữa.

 

[Hoàng Nhật Phương]

Xin chào ongke0711,
OT thử đăng nhập với User vừa mới tạo và thử với câu lệnh sau:
UPDATE [OT_TEST].[dbo].[ODER] SET MA_HANG= 'MH1102' WHERE STT=2;
Hệ thống báo:
The UPDATE permission was denied on the object 'ODER', database 'OT_TEST', schema 'dbo'.
Như vậy là OK rồi phải không Anh )

Đúng rồi OT đã thử chính xác là vậy.
Khi chuyển User chính thì câu lệnh trên OK, với thông báo "(1 row affected)"
Sau dó OT kiểm tra lại dữ liệu đã thay đổi, còn với User mới tạo thì không..
OT Cảm ơn Anh ongke0711 nhiều ạ

 

[tuandny1]

Định nghĩa thế nào là một dòng?

Sub test()
Dim cn As Object, rst As Object
Dim mySQL As String
Set cn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
If Val(Application.Version) < 12 Then
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"";"
Else
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"";"
End If
.Open
End With
mySQL = "select * from BC_TONG where C5 = '" & (Sheet6.Range("S2")) & "'" & _
"and C6 = '" & (Sheet6.Range("T2")) & "'" & _
"and C7 = '" & (Sheet6.Range("U2")) & "'" & _
"and C8*1 = '" & (Sheet6.Range("V2") * 1) & "'" & _
"and C9 = '" & (Sheet6.Range("W2")) & "'" & _
"and C10 = '" & (Sheet6.Range("X2")) & "'" & _
"and C11 = '" & (Sheet6.Range("Y2")) & "' "
Set rst = cn.Execute(mySQL)
Range("o3").CopyFromRecordset rst
' tim dong chua mySQL trong du lieu
Dim i As Long
'i = Sheet6.Range("A1:M20000").Find("rst").Row
Set rst = Nothing: Set cn = Nothing

End Sub
Bác xem giúp

 

[VetMini]

mySQL = "select * from BC_TONG where C5 = '" & (Sheet6.Range("S2")) & "'" & _
"and C6 = '" & (Sheet6.Range("T2")) & "'" & _
"and C7 = '" & (Sheet6.Range("U2")) & "'" & _
"and C8*1 = '" & (Sheet6.Range("V2") * 1) & "'" & _
"and C9 = '" & (Sheet6.Range("W2")) & "'" & _
"and C10 = '" & (Sheet6.Range("X2")) & "'" & _
"and C11 = '" & (Sheet6.Range("Y2")) & "' "

Luật đáng ghi nhớ: khi viết câu string cho lệnh SQL, đừng bao giờ tiết kiệm số dấu cách.
Và do vậy:
Mẹo tốt để tránh lỗi khi viết một câu lệnh SQL: luôn luôn mở đầu chuỗi bằng dấu cách. Trừ phi chỗ đó là tham số.

mySQL = " select * from BC_TONG where C5 = '" & (Sheet6.Range("S2")) & "'" & _
" and C6 = '" & (Sheet6.Range("T2")) & "'" & _ (dấu cách thêm vào trước and, nhưng không trước dấu nháy ' , vì ' là một phần của tham số)
" and C7 = '" & (Sheet6.Range("U2")) & "'" & _
" and C8*1 = '" & (Sheet6.Range("V2") * 1) & "'" & _
" and C9 = '" & (Sheet6.Range("W2")) & "'" & _
" and C10 = '" & (Sheet6.Range("X2")) & "'" & _
" and C11 = '" & (Sheet6.Range("Y2")) & "' "

 

[ongke0711]

Trời đất, STT (?)
Còn gì là CSDL LH nữa.

Lúc nãy không để ý vụ này . Em OT xem lại cách thiết kế Table nhé, không cần mấy cột STT lãng phí này đâu.

 

[tuandny1]

Luật đáng ghi nhớ: khi viết câu string cho lệnh SQL, đừng bao giờ tiết kiệm số dấu cách.
Và do vậy:
Mẹo tốt để tránh lỗi khi viết một câu lệnh SQL: luôn luôn mở đầu chuỗi bằng dấu cách. Trừ phi chỗ đó là tham số.

mySQL = " select * from BC_TONG where C5 = '" & (Sheet6.Range("S2")) & "'" & _
" and C6 = '" & (Sheet6.Range("T2")) & "'" & _ (dấu cách thêm vào trước and, nhưng không trước dấu nháy ' , vì ' là một phần của tham số)
" and C7 = '" & (Sheet6.Range("U2")) & "'" & _
" and C8*1 = '" & (Sheet6.Range("V2") * 1) & "'" & _
" and C9 = '" & (Sheet6.Range("W2")) & "'" & _
" and C10 = '" & (Sheet6.Range("X2")) & "'" & _
" and C11 = '" & (Sheet6.Range("Y2")) & "' "

Câu lệnh SQL thì tạm ổn bác ạ , nhưng h em muốn tìm vị trí nó nằm ở hàng nào trong bảng BC_TONG , có phương án nào ko bác . Có thể trong mySQL có nhiều dữ liệu , mình biết tất cả số dòng chứa dữ liệu my SQL

 

[VetMini]

Tôi đã hứa với ban quản trị là tôi không phê phán các câu hỏi có từ viết tắt, nhưng tôi chưa hề nhượng bộ quyền từ chối không trả lới các câu hỏi ấy.

 

[Hoàng Nhật Phương]

Xin chào các bạn,
Nhờ các bạn xem & giúp tôi trường hợp sau với ạ:
Tôi có một dữ liệu ban đầu bảng(sheet) S, dùng câu lệnh SQL nào có thể ra được đầu ra dữ liệu như bảng(sheet) R.
Tổng hợp số lượng duy nhất theo tại bảng S theo các trường: CODE,INW,OUTW,TYPE
Nếu TYPE=2 thì bảng R sẽ tạo thêm các trường cột: INW2,OUTW2,QTY2 và nhập các dữ liệu duy nhất theo TYPE=2

Bảng(sheet) S:


Bảng(sheet) R:

 

[Hai Lúa Miền Tây]

Xin chào các bạn,
Nhờ các bạn xem & giúp tôi trường hợp sau với ạ:
Tôi có một dữ liệu ban đầu bảng(sheet) S, dùng câu lệnh SQL nào có thể ra được đầu ra dữ liệu như bảng(sheet) R.
Tổng hợp số lượng duy nhất theo tại bảng S theo các trường: CODE,INW,OUTW,TYPE
Nếu TYPE=2 thì bảng R sẽ tạo thêm các trường cột: INW2,OUTW2,QTY2 và nhập các dữ liệu duy nhất theo TYPE=2

Bảng(sheet) S:
View attachment 246841

Bảng(sheet) R:
View attachment 246842

Dùng UNION ALL với điều kiện TYPE=2 sau đó gom nhóm lại là được nhé.

 

[Hoàng Nhật Phương]

Dùng UNION ALL với điều kiện TYPE=2 sau đó gom nhóm lại là được nhé.

Dạ,làm phiền anh Hai Lúa Miền Tây,viết giúp OT cụ thể câu lệnh select này với ạ.
OT cảm ơn anh ạ.

 

[Hai Lúa Miền Tây]

Dạ,làm phiền anh Hai Lúa Miền Tây,viết giúp OT cụ thể câu lệnh select này với ạ.
OT cảm ơn anh ạ.

Em thử code sau nhé:

Sub GomDL_HLMT()
    Dim strSQL As String
    strSQL = "Select CODE,INW,OUTW,QTY,'' AS INW2,'' AS OUTW2,0 AS QTY2,TYPE  from [S$] WHERE TYPE=1 UNION ALL Select CODE,'','',0,INW,OUTW,QTY,TYPE  from [S$] WHERE TYPE=2"
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName)
        Sheet2.Range("A2").CopyFromRecordset .Execute("Select CODE,INW,OUTW,SUM(QTY),INW2,OUTW2,SUM(QTY2),TYPE FROM (" & strSQL & ") GROUP BY CODE,TYPE,INW,OUTW,INW2,OUTW2")
    End With
   
End Sub

 

[Hoàng Nhật Phương]

Em thử code sau nhé:

Sub GomDL_HLMT()
    Dim strSQL As String
    strSQL = "Select CODE,INW,OUTW,QTY,'' AS INW2,'' AS OUTW2,0 AS QTY2,TYPE  from [S$] WHERE TYPE=1 UNION ALL Select CODE,'','',0,INW,OUTW,QTY,TYPE  from [S$] WHERE TYPE=2"
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName)
        Sheet2.Range("A2").CopyFromRecordset .Execute("Select CODE,INW,OUTW,SUM(QTY),INW2,OUTW2,SUM(QTY2),TYPE FROM (" & strSQL & ") GROUP BY CODE,TYPE,INW,OUTW,INW2,OUTW2")
    End With
  
End Sub

Lơi hại , lợi hại quá, ngôn SQL thật tuyệt vời.
Cảm ơn anh Hai Lúa nhiều ạ

 

[Hai Lúa Miền Tây]

Lơi hại , lợi hại quá, ngôn SQL thật tuyệt vời.
Cảm ơn anh Hai Lúa nhiều ạ

Nếu như tôi thì tôi làm báo cáo bằng CROSSTAB Query như hình sau:



Bởi vì có thể trong cột TYPE có nhiều loại mà ta chưa biết nó là loại nào.

 

[Hoàng Nhật Phương]

Nếu như tôi thì tôi làm báo cáo bằng CROSSTAB Query như hình sau:

View attachment 246847

Bởi vì có thể trong cột TYPE có nhiều loại mà ta chưa biết nó là loại nào.

" CROSSTAB Query " là gì vậy anh Hai Lúa? Nó có select như kiểu câu lệnh bài #142 của anh không ạ?
Nhìn kết quả đúng là gọn và dễ hiểu hơn báo cáo trên của OT ạ.
Úi trời,câu lệnh #142 của anh mà OT đưa vào áp dụng thử thêm nhiều trường và thêm các điều kiện lọc ngày,tháng,mã hàng.... mà câu lệnh dài quá
Cảm ơn anh Hai Lúa đã giúp đỡ.

 

[Hai Lúa Miền Tây]

" CROSSTAB Query " là gì vậy anh Hai Lúa? Nó có select như kiểu câu lệnh bài #142 của anh không ạ?
Nhìn kết quả đúng là gọn và dễ hiểu hơn báo cáo trên của OT ạ.
Úi trời,câu lệnh #142 của anh mà OT đưa vào áp dụng thử thêm nhiều trường và thêm các điều kiện lọc ngày,tháng,mã hàng.... mà câu lệnh dài quá
Cảm ơn anh Hai Lúa đã giúp đỡ.

Do vậy anh mới nói phải dùng Crosstab Query như bài #144, em thử code như sau nhé:

Sub GomDL_HLMT1()
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName)
        Sheet2.Range("A7").CopyFromRecordset .Execute("TRANSFORM SUM(QTY) SELECT CODE,INW,OUTW,SUM(QTY) FROM [S$] GROUP BY CODE,TYPE,INW,OUTW PIVOT TYPE")
    End With
    
End Sub

 

[Hoàng Nhật Phương]

Do vậy anh mới nói phải dùng Crosstab Query như bài #144, em thử code như sau nhé:

Sub GomDL_HLMT1()
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName)
        Sheet2.Range("A7").CopyFromRecordset .Execute("TRANSFORM SUM(QTY) SELECT CODE,INW,OUTW,SUM(QTY) FROM [S$] GROUP BY CODE,TYPE,INW,OUTW PIVOT TYPE")
    End With
   
End Sub

Cách này đúng là ngắn hơn cách trên nhiều, bớt được hẳn 2 câu lệnh Select dài ngoằng .
Cảm ơn anh đã chỉ cho OT thêm một cách ạ.
OT chúc anh Hai Lúa sức khỏe tốt.

 

[Hai Lúa Miền Tây]

Cách này đúng là ngắn hơn cách trên nhiều, bớt được hẳn 2 câu lệnh Select dài ngoằng .
Cảm ơn anh đã chỉ cho OT thêm một cách ạ.
OT chúc anh Hai Lúa sức khỏe tốt.

Thêm cho em cái tiêu đề vào luôn nhé, bây giờ không cần quan tâm đến cột TYPE có bao nhiêu loại theo chuẩn.

Sub GomDL_HLMT2()
    Dim fldName, i As Byte
    With CreateObject("ADODB.Recordset")
        .Open ("TRANSFORM SUM(QTY) SELECT CODE,INW,OUTW,SUM(QTY) AS TOTAL_QTY FROM [S$] GROUP BY CODE,TYPE,INW,OUTW PIVOT TYPE"), "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
        For Each fldName In .Fields
            i = i + 1
            Sheet2.Cells(6, i) = fldName.Name
        Next
        Sheet2.Range("A7").CopyFromRecordset .DataSource
    End With
   
End Sub

 

[Hoàng Nhật Phương]

Thêm cho em cái tiêu đề vào luôn nhé, bây giờ không cần quan tâm đến cột TYPE có bao nhiêu cột theo chuẩn.

Sub GomDL_HLMT2()
    Dim fldName, i As Byte
    With CreateObject("ADODB.Recordset")
        .Open ("TRANSFORM SUM(QTY) SELECT CODE,INW,OUTW,SUM(QTY) AS TOTAL_QTY FROM [S$] GROUP BY CODE,TYPE,INW,OUTW PIVOT TYPE"), "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
        For Each fldName In .Fields
            i = i + 1
            Sheet2.Cells(6, i) = fldName.Name
        Next
        Sheet2.Range("A7").CopyFromRecordset .DataSource
    End With
   
End Sub

Code xịn xò quá , cảm ơn anh nhiều ạ.

 

[ongke0711]

Thêm cho em cái tiêu đề vào luôn nhé, bây giờ không cần quan tâm đến cột TYPE có bao nhiêu loại theo chuẩn.

Sub GomDL_HLMT2()
...
        .Open ("TRANSFORM SUM(QTY) SELECT CODE,INW,OUTW,SUM(QTY) AS TOTAL_QTY FROM [S$] GROUP BY CODE,TYPE,INW,OUTW PIVOT TYPE"), "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
       ..
 
End Sub

CrossTab Query thường biến động số cột tuỳ theo dữ liệu (cụ thể là cột TYPE trong ví dụ này) do đó tôi thường đưa vô liệt kê đủ các cột TYPE vào câu lệnh SQL để cố định số cột TYPE, không trồi ra thụt vào .

"TRANSFORM SUM(QTY) SELECT CODE,INW,OUTW,SUM(QTY) AS TOTAL_QTY FROM [S$] GROUP BY CODE,TYPE,INW,OUTW PIVOT TYPE IN (1,2,3,4,5) "

 

[Hoàng Nhật Phương]

Xin chào các bạn,
OT có 2 bảng dữ liệu ban đầu TDK(tồn đầu kỳ) & bảng NX (nhập xuất),nhờ các bạn xem và giúp đỡ câu lệnh truy vấn SQL để ra kết quả tại bảng NXT ạ:

1.Bảng TDK:

2.Bảng NX:


Kết quả mong muốn sau khi thực hiện câu lệnh truy vấn SQL ra kết quả như bảng NXT ạ:

 

[excel_lv1.5]

Xin chào các bạn,
OT có 2 bảng dữ liệu ban đầu TDK(tồn đầu kỳ) & bảng NX (nhập xuất),nhờ các bạn xem và giúp đỡ câu lệnh truy vấn SQL để ra kết quả tại bảng NXT ạ:

1.Bảng TDK:
View attachment 246895
2.Bảng NX:
View attachment 246897

Kết quả mong muốn sau khi thực hiện câu lệnh truy vấn SQL ra kết quả như bảng NXT ạ:
View attachment 246898

Tôi có thể dùng power pivot cho bài này không bạn!

 

[Hoàng Nhật Phương]

Tôi có thể dùng power pivot cho bài này không bạn!

Xin chào bạn,
Cảm ơn bạn đã quan tâm đến bài viết này của OT.
Dạ vâng bạn có thể sử dụng power pivot, hoặc Vba đều được ạ... các cách này sẽ giúp ích để OT tham khảo và ứng dụng sang vấn đề khác ạ.

Còn với trường hợp này OT cần câu lệnh truy vấn để lấy dữ liệu từ DB về ạ.
Rất mong được bạn và mọi người giúp đỡ.

 

[excel_lv1.5]

Xin chào bạn,
Cảm ơn bạn đã quan tâm đến bài viết này của OT.
Dạ vâng bạn có thể sử dụng power pivot, hoặc Vba đều được ạ... các cách này sẽ giúp ích để OT tham khảo và ứng dụng sang vấn đề khác ạ.

Còn với trường hợp này OT cần câu lệnh truy vấn để lấy dữ liệu từ DB về ạ.
Rất mong được bạn và mọi người giúp đỡ.

Ah, tôi hiểu nhầm vấn đề, mà bạn connect với DB SQL Server hay Excel!

 

[Hoàng Nhật Phương]

Ah, tôi hiểu nhầm vấn đề, mà bạn connect với DB SQL Server hay Excel!

Dạ OT kết nối với DB SQL Server ạ.
OT chỉ cần câu lệnh truy vấn và không lệnh kết nối và đóng kết nối đến DB nữa ạ.
Ví dụ như bài 142 ở trên OT có thể lấy câu lệnh "Select..." để sử dụng ạ.
còn các câu lệnh kết nối ADO trong excel mang tính test kết quả thay vì trên môi trường SQL thì test trên Excel ạ.

 

[excel_lv1.5]

Dạ OT kết nối với DB SQL Server ạ.
OT chỉ cần câu lệnh truy vấn và không lệnh kết nối và đóng kết nối đến DB nữa ạ.
Ví dụ như bài 142 ở trên OT có thể lấy câu lệnh "Select..." để sử dụng ạ.
còn các câu lệnh kết nối ADO trong excel mang tính test kết quả thay vì trên môi trường SQL thì test trên Excel ạ.

Tôi không có dữ liệu để test nên không biết chạy được không, nếu SQL thì tôi viết vầy (giả sử bạn có một bảng danh mục hàng [Category])

select a.[ma_hang],
sum(b.[so_luong]) [tdk],
sum(case when c.[kieu]='n' then c.[so_luong] else 0 end) [nhap],
sum(case when c.[kieu]='x' then c.[so_luong] else 0 end) [xuat],
sum(b.[so_luong])+sum(case when c.[kieu]='n' then c.[so_luong] else 0 end)-sum(case when c.[kieu]='x' then c.[so_luong] else 0 end)  [ton]
from [category] a
left join [TDK] b on a.[ma_hang]=b.[ma_hang]
left join [NX] c on a.[ma_hang]=c.[ma_hang]
group by a.[ma_hang]
order by a.[ma_hang]

 

[Hoàng Nhật Phương]

Tôi không có dữ liệu để test nên không biết chạy được không, nếu SQL thì tôi viết vầy (giả sử bạn có một bảng danh mục hàng [Category]

select a.[ma_hang],
sum(b.[so_luong]) [tdk],
sum(case when c.[kieu]='n' then c.[so_luong] else 0 end) [nhap],
sum(case when c.[kieu]='x' then c.[so_luong] else 0 end) [xuat],
sum(b.[so_luong])+sum(case when c.[kieu]='n' then c.[so_luong] else 0 end)-sum(case when c.[kieu]='x' then c.[so_luong] else 0 end)  [ton]
from [category] a
left join [TDK] b on a.[ma_hang]=b.[ma_hang]
left join [NX] c on a.[ma_hang]=c.[ma_hang]
group by a.[ma_hang]
order by a.[ma_hang]

Cảm ơn bạn nhiều, đúng như vậy OT chỉ cần câu lệnh truy vấn trong SQL dạng này ạ.

Úi nghĩa là phải cần thêm một bảng danh mục mã hàng để tham chiếu qua ạ.
Xin lỗi OT sơ ý cứ nghĩ sử dụng câu lệnh where mã hàng =N'.. '
Giờ OT mới hiểu thêm nếu lấy tất cả mã hàng là phải có thêm bảng danh mục mã hàng.
Cảm ơn bạn nhiều, ngày mai có điều kiện OT sẽ thử và thông tin lại ở đây ạ.
Chúc bạn ngủ ngon.

 

[HieuCD]

Tôi không có dữ liệu để test nên không biết chạy được không, nếu SQL thì tôi viết vầy (giả sử bạn có một bảng danh mục hàng [Category])

select a.[ma_hang],
sum(b.[so_luong]) [tdk],
sum(case when c.[kieu]='n' then c.[so_luong] else 0 end) [nhap],
sum(case when c.[kieu]='x' then c.[so_luong] else 0 end) [xuat],
sum(b.[so_luong])+sum(case when c.[kieu]='n' then c.[so_luong] else 0 end)-sum(case when c.[kieu]='x' then c.[so_luong] else 0 end)  [ton]
from [category] a
left join [TDK] b on a.[ma_hang]=b.[ma_hang]
left join [NX] c on a.[ma_hang]=c.[ma_hang]
group by a.[ma_hang]
order by a.[ma_hang]

Không cần bảng danh mục hàng được không bạn

 

[Hoàng Nhật Phương]

Không cần bảng danh mục hàng được không bạn

Con chào Bác HieuCD,
Bác ngủ muộn vậy ạ.
Bác có câu lệnh nào khác, Bác cho con tham khảo thêm với.
Con cảm ơn Bác HieCD.

 

[excel_lv1.5]

Không cần bảng danh mục hàng được không bạn

Thường thì trong DB sẽ có bảng danh mục, muốn tạo riêng thì có thể tạo từ 2 bảng [TDK] và [NX]

select DISTINCT a.[ma_hang]
into #category
from
(
    select a.[ma_hang]
    from [TDK] a
    union all
    select a.[ma_hang]
    from [NX] a
) a

 

[Hoàng Nhật Phương]

Thường thì trong DB sẽ có bảng danh mục, muốn tạo riêng thì có thể tạo từ 2 bảng [TDK] và [NX]

select DISTINCT a.[ma_hang]
into #category
from
(
    select a.[ma_hang]
    from [TDK] a
    union all
    select a.[ma_hang]
    from [NX] a
) a

Cảm ơn bạn nhiều ạ,
Đúng là DB có bảng danh mục hàng thường gọi là chung là masterItem ạ.
Giả sử không sử dụng bảng danh mục hàng có sẵn đó và nếu gộp chung câu lệnh này lên cùng với câu lệnh tính tồn kho thì câu lệnh mới sẽ sửa như thế nào vậy bạn?

 

[excel_lv1.5]

Cảm ơn bạn nhiều, đúng như vậy OT chỉ cần câu lệnh truy vấn trong SQL dạng này ạ.

Úi nghĩa là phải cần thêm một bảng danh mục mã hàng để tham chiếu qua ạ.
Xin lỗi OT sơ ý cứ nghĩ sử dụng câu lệnh where mã hàng =N'.. '
Giờ OT mới hiểu thêm nếu lấy tất cả mã hàng là phải có thêm bảng danh mục mã hàng.
Cảm ơn bạn nhiều, ngày mai có điều kiện OT sẽ thử và thông tin lại ở đây ạ.
Chúc bạn ngủ ngon.

Thực ra vẫn còn một cách làm khác là join 2 bảng TDK và NX lại rồi pivot

Bài đã được tự động gộp: 7/10/20

Cảm ơn bạn nhiều ạ,
Đúng là DB có bảng danh mục hàng thường gọi là chung là masterItem ạ.
Giả sử không sử dụng bảng danh mục hàng có sẵn đó và nếu gộp chung câu lệnh này lên cùng với câu lệnh tính tồn kho thì câu lệnh mới sẽ sửa như thế nào vậy bạn?

Bạn copy 2 câu đó lại chạy chung là được, câu tạo Category chạy trước

 

[Hoàng Nhật Phương]

Thực ra vẫn còn một cách làm khác là join 2 bảng TDK và NX lại rồi pivot

Ôi, bạn biết cách nào thì chỉ thêm cho OT với ạ, cách nào dễ ứng dụng và xuất ra được báo cáo theo nhu cầu gần gũi và dễ dàng sử dụng thì OT sẽ tham khảo để ứng dụng ạ.

Bài đã được tự động gộp: 7/10/20

Thực ra vẫn còn một cách làm khác là join 2 bảng TDK và NX lại rồi pivot

Bài đã được tự động gộp: 7/10/20

Bạn copy 2 câu đó lại chạy chung là được, câu tạo Category chạy trước

OT đã từng thử chạy nhiều câu lệnh truy vấn cùng lúc nhưng đó là trong SQL, mỗi câu lệnh select độc lập sẽ cho ra kết quả của mỗi bản, như vậy nhiều câu select riêng sẽ cho ra nhiều bảng dữ liệu kết quả. Còn trong code Excel sau khi thực hiện câu lệnh kết nối đến DB thì có thể chạy kiểu đó được không ạ, hay phải ghép chúng lại thành một chuỗi.
sáng mai đến cơ quan OT mới có điều kiện để thử được ạ.
Cảm ơn bạn nhiều.

 

[excel_lv1.5]

Ôi, bạn biết cách nào thì chỉ thêm cho OT với ạ, cách nào dễ ứng dụng và xuất ra được báo cáo theo nhu cầu gần gũi và dễ dàng sử dụng thì OT sẽ tham khảo để ứng dụng ạ.

Bài đã được tự động gộp: 7/10/20

OT đã từng thử chạy nhiều câu lệnh truy vấn cùng lúc nhưng đó là trong SQL, còn trong code Excel sau khi thực hiện câu lệnh kết nối đến DB thì có thể chạy kiểu đó được không ạ, hay phải ghép chúng lại thành một chuỗi.
sáng mai đến cơ quan OT mới có điều kiện để thử được ạ.
Cảm ơn bạn nhiều.

Trong ADO hình như việc ghép 2 câu lệnh như vậy chạy không được , bạn có thể test thông qua connect SQL của excel được nó cho phép ghép 2 câu lệnh như vậy
Đây là trường hợp dùng pivot (tôi cũng chưa test chỉ viết trên word)

select a.[kieu],a.[ma_hang], a.[so_luong]
from
(
    select 'TDK' [kieu],a.[ma_hang], a.[so_luong]
    from [tdk] a
    union all
    select a.[kieu],a.[ma_hang], a.[so_luong]
    from [nx] a
) a
pivot
(
sum([so_luong])
for [kieu] in ('tdk','n','x')
) b

 

[Hoàng Nhật Phương]

Trong ADO hình như việc ghép 2 câu lệnh như vậy chạy không được , bạn có thể test thông qua connect SQL của excel được nó cho phép ghép 2 câu lệnh như vậy
Đây là trường hợp dùng pivot (tôi cũng chưa test chỉ viết trên word)

select a.[kieu],a.[ma_hang], a.[so_luong]
from
(
    select 'TDK' [kieu],a.[ma_hang], a.[so_luong]
    from [tdk] a
    union all
    select a.[kieu],a.[ma_hang], a.[so_luong]
    from [nx] a
) a
pivot
(
sum([so_luong])
for [kieu] in ('tdk','n','x')
) b

Dạ

Trong ADO hình như việc ghép 2 câu lệnh như vậy chạy không được , bạn có thể test thông qua connect SQL của excel được nó cho phép ghép 2 câu lệnh như vậy
Đây là trường hợp dùng pivot (tôi cũng chưa test chỉ viết trên word)

select a.[kieu],a.[ma_hang], a.[so_luong]
from
(
    select 'TDK' [kieu],a.[ma_hang], a.[so_luong]
    from [tdk] a
    union all
    select a.[kieu],a.[ma_hang], a.[so_luong]
    from [nx] a
) a
pivot
(
sum([so_luong])
for [kieu] in ('tdk','n','x')
) b

Cảm ơn bạn rất nhiều, OT cũng nghĩ trong codeExcel không chạy kiểu đó lên bài viết phía trên OT có sửa lại nói rõ hơn khi nhiều câu lệnh truy vấn cùng lúc trong SQL.
Cảm ơn bạn nhiểu những kiến thức thức rất cần thiết cho OT mà OT không thể học và nhớ được.
OT ngủ đây ạ, chúc bạn và mọi người ngon giấc.

 

[Hoàng Nhật Phương]

Tôi không có dữ liệu để test nên không biết chạy được không, nếu SQL thì tôi viết vầy (giả sử bạn có một bảng danh mục hàng [Category])

select a.[ma_hang],
sum(b.[so_luong]) [tdk],
sum(case when c.[kieu]='n' then c.[so_luong] else 0 end) [nhap],
sum(case when c.[kieu]='x' then c.[so_luong] else 0 end) [xuat],
sum(b.[so_luong])+sum(case when c.[kieu]='n' then c.[so_luong] else 0 end)-sum(case when c.[kieu]='x' then c.[so_luong] else 0 end)  [ton]
from [category] a
left join [TDK] b on a.[ma_hang]=b.[ma_hang]
left join [NX] c on a.[ma_hang]=c.[ma_hang]
group by a.[ma_hang]
order by a.[ma_hang]

Xin chào excel_lv1.5
OT đã thêm bảng DMHH (danh mục hàng)

Và thử code bài #156 & #164 thì cả 2 code chạy đều bị lỗi, nhờ các bạn xem & giúp đỡ OT với ạ:

'Error: Unrecognized keyword WHEN.
Sub Xuat_Nhap_Ton_SQL()
    Dim strSQL As String
    strSQL = "select a.[ma_hang]," & _
            "sum(b.[so_luong]) [tdk]," & _
            "sum(case when c.[kieu]='n' then c.[so_luong] else 0 end) [nhap]," & _
            "sum(case when c.[kieu]='x' then c.[so_luong] else 0 end) [xuat]," & _
            "sum(b.[so_luong])+sum(case when c.[kieu]='n' then c.[so_luong] else 0 end)-sum(case when c.[kieu]='x' then c.[so_luong] else 0 end)  [ton] " & _
            "from [DMHH] a " & _
            "left join [TDK] b on a.[ma_hang]=b.[ma_hang] " & _
            "left join [NX] c on a.[ma_hang]=c.[ma_hang] " & _
            "group by a.[ma_hang] " & _
            "order by a.[ma_hang]"
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName)
        Sheet6.Range("G1").CopyFromRecordset .Execute(strSQL)
    End With
End Sub

'Error: Syntax error in FROM clause.
Sub Xuat_Nhap_Ton_SQL_2()
    Dim strSQL As String
       strSQL = "select a.[kieu],a.[ma_hang], a.[so_luong] " & _
                "from (" & _
                      "select 'TDK' [kieu],a.[ma_hang], a.[so_luong] from [tdk] a " & _
                      "Union all " & _
                      "select a.[kieu],a.[ma_hang], a.[so_luong] from [nx] a " & _
                      ") a " & _
                "pivot " & _
                "(Sum ([so_luong])for [kieu] in ('tdk','n','x')) b"
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName)
        Sheet6.Range("G1").CopyFromRecordset .Execute(strSQL)
    End With
End Sub

 

[Hoàng Nhật Phương]

Tôi không có dữ liệu để test nên không biết chạy được không, nếu SQL thì tôi viết vầy (giả sử bạn có một bảng danh mục hàng [Category])

select a.[ma_hang],
sum(b.[so_luong]) [tdk],
sum(case when c.[kieu]='n' then c.[so_luong] else 0 end) [nhap],
sum(case when c.[kieu]='x' then c.[so_luong] else 0 end) [xuat],
sum(b.[so_luong])+sum(case when c.[kieu]='n' then c.[so_luong] else 0 end)-sum(case when c.[kieu]='x' then c.[so_luong] else 0 end)  [ton]
from [category] a
left join [TDK] b on a.[ma_hang]=b.[ma_hang]
left join [NX] c on a.[ma_hang]=c.[ma_hang]
group by a.[ma_hang]
order by a.[ma_hang]

Chào Bạn,
Câu lệnh truy vấn này OT thử trong môi trường SQL, OK rồi ạ.
Xin hỏi thêm bạn nếu OT muốn truy vấn phân biệt theo một trường nữa (KHO_LUU_TRU) thì câu lệnh này sẽ phải sửa lại như thế nào ạ.
OT đã bổ sung trường 'KHO_LUU_TRU' vào bảng 'TDK' và 'NX' như ảnh kèm, nhờ bạn và mọi người xem giúp ạ:

 

[ongke0711]

Xin hỏi thêm bạn nếu OT muốn truy vấn phân biệt theo một trường nữa (KHO_LUU_TRU) thì câu lệnh này sẽ phải sửa lại như thế nào ạ.
OT đã bổ sung trường 'KHO_LUU_TRU' vào bảng 'TDK' và 'NX' như ảnh kèm, nhờ bạn và mọi người xem giúp ạ:

Anh góp ý vụ tính tồn kho này chút nhé. Vì xem mấy bài ở trên em đưa dữ liệu đã trích xuất ra theo từng mã hàng rồi nên cách xử lý sẽ khác. Thông thường cái tác vụ báo cáo NXT là: tính NXT của các mã hàng (hoặc 1 mã hàng) trong khoảng thời gian nào đó cần truy vấn. Do đó tốt nhất là em nên đưa cái CSDL để tính NXT có các thông tin: tất cả các mã hàng, ngày tháng nhập xuất, kho; Tồn đầu kỳ của tất cả các mã hàng - kho tồn - tháng/năm tồn (bảng TDK theo tháng phải khong em?). Khi đó câu lệnh truy vấn sẽ đầy đủ hơn, kết quả trả về cũng đáp ứng như cầu nhiều hơn.

 

[Hoàng Nhật Phương]

Anh góp ý vụ tính tồn kho này chút nhé. Vì xem mấy bài ở trên em đưa dữ liệu đã trích xuất ra theo từng mã hàng rồi nên cách xử lý sẽ khác. Thông thường cái tác vụ báo cáo NXT là: tính NXT của các mã hàng (hoặc 1 mã hàng) trong khoảng thời gian nào đó cần truy vấn. Do đó tốt nhất là em nên đưa cái CSDL để tính NXT có các thông tin: tất cả các mã hàng, ngày tháng nhập xuất, kho; Tồn đầu kỳ của tất cả các mã hàng - kho tồn - tháng/năm tồn (bảng TDK theo tháng phải khong em?). Khi đó câu lệnh truy vấn sẽ đầy đủ hơn, kết quả trả về cũng đáp ứng như cầu nhiều hơn.

Xin chào anh ongke0711,
Dạ vâng mới đầu OT cũng nghĩ như vậy nhưng OT thấy để ít trường như vậy để câu lệnh nó ngắn ạ nếu để nhiều trường sợ câu lệnh nó dài rồi rối lên.
Vậy OT sẽ bổ sung thêm 1 số trường ngày tháng (các trường cơ bản) sau đó gửi lại data lên đây nhờ anh và mọi người xem giúp ạ.
Cảm ơn anh đã góp ý ạ.
OT

 

[tigertiger]

Ôi, bạn biết cách nào thì chỉ thêm cho OT với ạ, cách nào dễ ứng dụng và xuất ra được báo cáo theo nhu cầu gần gũi và dễ dàng sử dụng thì OT sẽ tham khảo để ứng dụng ạ.

Bài đã được tự động gộp: 7/10/20

OT đã từng thử chạy nhiều câu lệnh truy vấn cùng lúc nhưng đó là trong SQL, mỗi câu lệnh select độc lập sẽ cho ra kết quả của mỗi bản, như vậy nhiều câu select riêng sẽ cho ra nhiều bảng dữ liệu kết quả. Còn trong code Excel sau khi thực hiện câu lệnh kết nối đến DB thì có thể chạy kiểu đó được không ạ, hay phải ghép chúng lại thành một chuỗi.
sáng mai đến cơ quan OT mới có điều kiện để thử được ạ.
Cảm ơn bạn nhiều.

Mỗi lần gửi thực thi SQL qua ADO trong Excel thì chỉ gửi 1 câu lệnh mà thôi
Ghép thì có UNION, JOIN... nhưng đó là ghép thành 1 lệnh

Muốn nhiều lệnh thì cần gửi thực thi nhiều lần (dùng chung 1 kết nối)

 

[HieuCD]

Xin chào excel_lv1.5
OT đã thêm bảng DMHH (danh mục hàng)
View attachment 246920
Và thử code bài #156 & #164 thì cả 2 code chạy đều bị lỗi, nhờ các bạn xem & giúp đỡ OT với ạ:

'Error: Unrecognized keyword WHEN.
Sub Xuat_Nhap_Ton_SQL()
    Dim strSQL As String
    strSQL = "select a.[ma_hang]," & _
            "sum(b.[so_luong]) [tdk]," & _
            "sum(case when c.[kieu]='n' then c.[so_luong] else 0 end) [nhap]," & _
            "sum(case when c.[kieu]='x' then c.[so_luong] else 0 end) [xuat]," & _
            "sum(b.[so_luong])+sum(case when c.[kieu]='n' then c.[so_luong] else 0 end)-sum(case when c.[kieu]='x' then c.[so_luong] else 0 end)  [ton] " & _
            "from [DMHH] a " & _
            "left join [TDK] b on a.[ma_hang]=b.[ma_hang] " & _
            "left join [NX] c on a.[ma_hang]=c.[ma_hang] " & _
            "group by a.[ma_hang] " & _
            "order by a.[ma_hang]"
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName)
        Sheet6.Range("G1").CopyFromRecordset .Execute(strSQL)
    End With
End Sub

'Error: Syntax error in FROM clause.
Sub Xuat_Nhap_Ton_SQL_2()
    Dim strSQL As String
       strSQL = "select a.[kieu],a.[ma_hang], a.[so_luong] " & _
                "from (" & _
                      "select 'TDK' [kieu],a.[ma_hang], a.[so_luong] from [tdk] a " & _
                      "Union all " & _
                      "select a.[kieu],a.[ma_hang], a.[so_luong] from [nx] a " & _
                      ") a " & _
                "pivot " & _
                "(Sum ([so_luong])for [kieu] in ('tdk','n','x')) b"
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName)
        Sheet6.Range("G1").CopyFromRecordset .Execute(strSQL)
    End With
End Sub

Bạn phải viết theo kiểu Excel, ví dụ

Sub Xuat_Nhap_Ton_SQL()
    Dim strSQL As String
    strSQL = "select a.[ma_hang]," & _
            "sum(b.[so_luong])  ," & _
            "sum(-(c.[kieu]='N')* c.[so_luong])," & _
            "sum(-(c.[kieu]='X')* c.[so_luong])," & _
            "sum(iif(isnull(b.[so_luong]),0,b.[so_luong]))+sum(-(c.[kieu]='N')*c.[so_luong])-sum(-(c.[kieu]='X')* c.[so_luong])" & _
            "from ([DMHH$] a " & _
            "left join [TDK$] b on a.[ma_hang]=b.[ma_hang]) " & _
            "left join [NX$] c on a.[ma_hang]=c.[ma_hang] " & _
            "group by a.[ma_hang] " & _
            "order by a.[ma_hang]"
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName)
        Sheet6.Range("A6").CopyFromRecordset .Execute(strSQL)
    End With
End Sub

Chú ý lệnh:
sum(iif(isnull(b.[so_luong]),0,b.[so_luong]))

 

[Hoàng Nhật Phương]

Bạn phải viết theo kiểu Excel, ví dụ

Sub Xuat_Nhap_Ton_SQL()
    Dim strSQL As String
    strSQL = "select a.[ma_hang]," & _
            "sum(b.[so_luong])  ," & _
            "sum(-(c.[kieu]='N')* c.[so_luong])," & _
            "sum(-(c.[kieu]='X')* c.[so_luong])," & _
            "sum(iif(isnull(b.[so_luong]),0,b.[so_luong]))+sum(-(c.[kieu]='N')*c.[so_luong])-sum(-(c.[kieu]='X')* c.[so_luong])" & _
            "from ([DMHH$] a " & _
            "left join [TDK$] b on a.[ma_hang]=b.[ma_hang]) " & _
            "left join [NX$] c on a.[ma_hang]=c.[ma_hang] " & _
            "group by a.[ma_hang] " & _
            "order by a.[ma_hang]"
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName)
        Sheet6.Range("A6").CopyFromRecordset .Execute(strSQL)
    End With
End Sub

Chú ý lệnh:
sum(iif(isnull(b.[so_luong]),0,b.[so_luong]))

Code chạy được rồi Bác HieuCD, con cảm ơn Bác.
Nhưng mà sao TDK của con chỉ có 150 mà code chạy ra tận 4800 , Bác xem giúp con với ạ.

 

[HieuCD]

Code chạy được rồi Bác HieuCD, con cảm ơn Bác.
Nhưng mà sao TDK của con chỉ có 150 mà code chạy ra tận 4800 , Bác xem giúp con với ạ.

Không hiểu tại sao luôn
Bạn @VetMini giải thích dùm mình

Sub Xuat_Nhap_Ton_SQL()
    Dim strSQL As String
    strSQL = "select a.[ma_hang]," & _
            "max(b.[so_luong])  ," & _
            "sum(-(c.[kieu]='N')* c.[so_luong])," & _
            "sum(-(c.[kieu]='X')* c.[so_luong])," & _
            "max(iif(isnull(b.[so_luong]),0,b.[so_luong]))+sum(-(c.[kieu]='N')*c.[so_luong])-sum(-(c.[kieu]='X')* c.[so_luong])" & _
            "from ([DMHH$] a " & _
            "left join [TDK$] b on a.[ma_hang]=b.[ma_hang]) " & _
            "left join [NX$] c on a.[ma_hang]=c.[ma_hang] " & _
            "group by a.[ma_hang] " & _
            "order by a.[ma_hang]"
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName)
        Sheet6.Range("A6").CopyFromRecordset .Execute(strSQL)
    End With
End Sub

 

[Hoàng Nhật Phương]

Không hiểu tại sao luôn

Sub Xuat_Nhap_Ton_SQL()
    Dim strSQL As String
    strSQL = "select a.[ma_hang]," & _
            "max(b.[so_luong])  ," & _
            "sum(-(c.[kieu]='N')* c.[so_luong])," & _
            "sum(-(c.[kieu]='X')* c.[so_luong])," & _
            "max(iif(isnull(b.[so_luong]),0,b.[so_luong]))+sum(-(c.[kieu]='N')*c.[so_luong])-sum(-(c.[kieu]='X')* c.[so_luong])" & _
            "from ([DMHH$] a " & _
            "left join [TDK$] b on a.[ma_hang]=b.[ma_hang]) " & _
            "left join [NX$] c on a.[ma_hang]=c.[ma_hang] " & _
            "group by a.[ma_hang] " & _
            "order by a.[ma_hang]"
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName)
        Sheet6.Range("A6").CopyFromRecordset .Execute(strSQL)
    End With
End Sub

Con cảm ơn Bác, code chạy ra kết quả đúng ý con rồi.
Hic, Bác không hiểu chắc con xác định khỏi tìm hiểu luôn ạ

 

[excel_lv1.5]

Chào Bạn,
Câu lệnh truy vấn này OT thử trong môi trường SQL, OK rồi ạ.
Xin hỏi thêm bạn nếu OT muốn truy vấn phân biệt theo một trường nữa (KHO_LUU_TRU) thì câu lệnh này sẽ phải sửa lại như thế nào ạ.
OT đã bổ sung trường 'KHO_LUU_TRU' vào bảng 'TDK' và 'NX' như ảnh kèm, nhờ bạn và mọi người xem giúp ạ:
View attachment 246931

Vậy bên bảng DMHH bảng cũng phải có kho luôn nhe bạn, tôi sửa lại trong môi trường SQL Server và khi DMHH có thêm cột kho

select a.[ma_hang],a.[KHO_LUU_TRU],
sum(b.[so_luong]) [tdk],
sum(case when c.[kieu]='n' then c.[so_luong] else 0 end) [nhap],
sum(case when c.[kieu]='x' then c.[so_luong] else 0 end) [xuat],
sum(b.[so_luong])+sum(case when c.[kieu]='n' then c.[so_luong] else 0 end)-sum(case when c.[kieu]='x' then c.[so_luong] else 0 end)  [ton]
from [category] a
left join [TDK] b on a.[ma_hang]=b.[ma_hang] and a.[KHO_LUU_TRU]=b.[KHO_LUU_TRU]
left join [NX] c on a.[ma_hang]=c.[ma_hang] and a.[KHO_LUU_TRU]=c.[KHO_LUU_TRU]
group by a.[ma_hang], a.[KHO_LUU_TRU]
order by a.[ma_hang], a.[KHO_LUU_TRU]

Còn connect qua SQL của Access bạn tùy biến lại

 

[Hoàng Nhật Phương]

Không hiểu tại sao luôn
Bạn @VetMini giải thích dùm mình

Sub Xuat_Nhap_Ton_SQL()
    Dim strSQL As String
    strSQL = "select a.[ma_hang]," & _
            "max(b.[so_luong])  ," & _
            "sum(-(c.[kieu]='N')* c.[so_luong])," & _
            "sum(-(c.[kieu]='X')* c.[so_luong])," & _
            "max(iif(isnull(b.[so_luong]),0,b.[so_luong]))+sum(-(c.[kieu]='N')*c.[so_luong])-sum(-(c.[kieu]='X')* c.[so_luong])" & _
            "from ([DMHH$] a " & _
            "left join [TDK$] b on a.[ma_hang]=b.[ma_hang]) " & _
            "left join [NX$] c on a.[ma_hang]=c.[ma_hang] " & _
            "group by a.[ma_hang] " & _
            "order by a.[ma_hang]"
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName)
        Sheet6.Range("A6").CopyFromRecordset .Execute(strSQL)
    End With
End Sub

Bác ơi câu lệnh này của Bác, 2 cái dấu "()" có gì khác biệt giữa Excel và SQL không ?
from ([DMHH$] a left join [TDK$] b on a.[ma_hang]=b.[ma_hang]) left join [NX$] c on a.[ma_hang]=c.[ma_hang]
mà con thấy đưa vào SQL nó kỳ quá ạ

Vậy bên bảng DMHH bảng cũng phải có kho luôn nhe bạn, tôi sửa lại trong môi trường SQL Server và khi DMHH có thêm cột kho

select a.[ma_hang],a.[KHO_LUU_TRU],
sum(b.[so_luong]) [tdk],
sum(case when c.[kieu]='n' then c.[so_luong] else 0 end) [nhap],
sum(case when c.[kieu]='x' then c.[so_luong] else 0 end) [xuat],
sum(b.[so_luong])+sum(case when c.[kieu]='n' then c.[so_luong] else 0 end)-sum(case when c.[kieu]='x' then c.[so_luong] else 0 end)  [ton]
from [category] a
left join [TDK] b on a.[ma_hang]=b.[ma_hang] and a.[KHO_LUU_TRU]=b.[KHO_LUU_TRU]
left join [NX] c on a.[ma_hang]=c.[ma_hang] and a.[KHO_LUU_TRU]=c.[KHO_LUU_TRU]
group by a.[ma_hang], a.[KHO_LUU_TRU]
order by a.[ma_hang], a.[KHO_LUU_TRU]

Còn connect qua SQL của Access bạn tùy biến lại

Cảm ơn bạn nhiều ạ, OT sẽ kiểm tra và thông tin lại ạ

Bài đã được tự động gộp: 7/10/20

Vậy bên bảng DMHH bảng cũng phải có kho luôn nhe bạn, tôi sửa lại trong môi trường SQL Server và khi DMHH có thêm cột kho

select a.[ma_hang],a.[KHO_LUU_TRU],
sum(b.[so_luong]) [tdk],
sum(case when c.[kieu]='n' then c.[so_luong] else 0 end) [nhap],
sum(case when c.[kieu]='x' then c.[so_luong] else 0 end) [xuat],
sum(b.[so_luong])+sum(case when c.[kieu]='n' then c.[so_luong] else 0 end)-sum(case when c.[kieu]='x' then c.[so_luong] else 0 end)  [ton]
from [category] a
left join [TDK] b on a.[ma_hang]=b.[ma_hang] and a.[KHO_LUU_TRU]=b.[KHO_LUU_TRU]
left join [NX] c on a.[ma_hang]=c.[ma_hang] and a.[KHO_LUU_TRU]=c.[KHO_LUU_TRU]
group by a.[ma_hang], a.[KHO_LUU_TRU]
order by a.[ma_hang], a.[KHO_LUU_TRU]

Còn connect qua SQL của Access bạn tùy biến lại

@excel_lv1.5 cảm ơn bạn đã quan tâm và giúp đỡ OT.
Bảng 'DMHH' không có Kho bạn ạ, mà nó nằm một bảng khác nữa ví dụ là bảng 'DMKHO' bạn ạ T_T

 

[HieuCD]

Bác ơi câu lệnh này của Bác, 2 cái dấu "()" có gì khác biệt giữa Excel và SQL không ?
from ([DMHH$] a left join [TDK$] b on a.[ma_hang]=b.[ma_hang]) left join [NX$] c on a.[ma_hang]=c.[ma_hang]
mà con thấy đưa vào SQL nó kỳ quá ạ




Cảm ơn bạn nhiều ạ, OT sẽ kiểm tra và thông tin lại ạ

Bài đã được tự động gộp: 7/10/20

@excel_lv1.5 cảm ơn bạn đã quan tâm và giúp đỡ OT.
Bảng 'DMHH' không có Kho bạn ạ, mà nó nằm một bảng khác nữa ví dụ là bảng 'DMKHO' bạn ạ T_T

Mình nghỉ nên có () mối liên kết sẽ tường minh hơn

 

[Hoàng Nhật Phương]

Vậy bên bảng DMHH bảng cũng phải có kho luôn nhe bạn, tôi sửa lại trong môi trường SQL Server và khi DMHH có thêm cột kho

select a.[ma_hang],a.[KHO_LUU_TRU],
sum(b.[so_luong]) [tdk],
sum(case when c.[kieu]='n' then c.[so_luong] else 0 end) [nhap],
sum(case when c.[kieu]='x' then c.[so_luong] else 0 end) [xuat],
sum(b.[so_luong])+sum(case when c.[kieu]='n' then c.[so_luong] else 0 end)-sum(case when c.[kieu]='x' then c.[so_luong] else 0 end)  [ton]
from [category] a
left join [TDK] b on a.[ma_hang]=b.[ma_hang] and a.[KHO_LUU_TRU]=b.[KHO_LUU_TRU]
left join [NX] c on a.[ma_hang]=c.[ma_hang] and a.[KHO_LUU_TRU]=c.[KHO_LUU_TRU]
group by a.[ma_hang], a.[KHO_LUU_TRU]
order by a.[ma_hang], a.[KHO_LUU_TRU]

Còn connect qua SQL của Access bạn tùy biến lại

Xin chào excel_lv1.5
OT test thử câu lệnh truy vấn trên của bạn và đã biết cách tham chiếu thêm đến bảng DMKHO, nhưng có một vấn đề đó là cái đoạn:
"sum(b.[so_luong]) [tdk],
sum(case when c.[kieu]='n' then c.[so_luong] else 0 end) [nhap],
sum(case when c.[kieu]='x' then c.[so_luong] else 0 end) [xuat],
sum(b.[so_luong])+sum(case when c.[kieu]='n' then c.[so_luong] else 0 end)-sum(case when c.[kieu]='x' then c.[so_luong] else 0 end) [ton]"
OT đang thấy ra kết quả không đúng ạ.
Ví dụ OT chỉ kiểm tra tồn đầu kỳ và so sánh với câu lệnh "sum(b.[so_luong]) [tdk]" dữ liệu là "150" nhưng kết quả chạy câu lệnh này là "33600"
Bạn xem giúp với ạ

 

[excel_lv1.5]

Xin chào excel_lv1.5
OT test thử câu lệnh truy vấn trên của bạn và đã biết cách tham chiếu thêm đến bảng DMKHO, nhưng có một vấn đề đó là cái đoạn:
"sum(b.[so_luong]) [tdk],
sum(case when c.[kieu]='n' then c.[so_luong] else 0 end) [nhap],
sum(case when c.[kieu]='x' then c.[so_luong] else 0 end) [xuat],
sum(b.[so_luong])+sum(case when c.[kieu]='n' then c.[so_luong] else 0 end)-sum(case when c.[kieu]='x' then c.[so_luong] else 0 end) [ton]"
OT đang thấy ra kết quả không đúng ạ.
Ví dụ OT chỉ kiểm tra tồn đầu kỳ và so sánh với câu lệnh "sum(b.[so_luong]) [tdk]" dữ liệu là "150" nhưng kết quả chạy câu lệnh này là "33600"
Bạn xem giúp với ạ

Bạn gửi cho tôi câu lệnh bạn điều chỉnh tôi xem thử!

 

[Hai Lúa Miền Tây]

Xin chào excel_lv1.5
OT test thử câu lệnh truy vấn trên của bạn và đã biết cách tham chiếu thêm đến bảng DMKHO, nhưng có một vấn đề đó là cái đoạn:
"sum(b.[so_luong]) [tdk],
sum(case when c.[kieu]='n' then c.[so_luong] else 0 end) [nhap],
sum(case when c.[kieu]='x' then c.[so_luong] else 0 end) [xuat],
sum(b.[so_luong])+sum(case when c.[kieu]='n' then c.[so_luong] else 0 end)-sum(case when c.[kieu]='x' then c.[so_luong] else 0 end) [ton]"
OT đang thấy ra kết quả không đúng ạ.
Ví dụ OT chỉ kiểm tra tồn đầu kỳ và so sánh với câu lệnh "sum(b.[so_luong]) [tdk]" dữ liệu là "150" nhưng kết quả chạy câu lệnh này là "33600"
Bạn xem giúp với ạ

Chỗ trống như hình là "A" hay trống vậy em?

 

[Hoàng Nhật Phương]

Bạn gửi cho tôi câu lệnh bạn điều chỉnh tôi xem thử!

Dạ đây ạ, OT gửi bạn nhờ bạn kiểm tra giúp ạ.

Select a.[MA_HANG],b.[KHO_LUU_TRU],c.[LOTNO],
sum(b.[QTY]) [TDK],
sum(case when c.[KIEU]='N' then c.[QTY] else 0 end) [NHAP],
sum(case when c.[KIEU]='X' then c.[QTY] else 0 end) [XUAT],
sum(b.[QTY])+sum(case when c.[KIEU]='N' then c.[QTY] else 0 end)-sum(case when c.[KIEU]='X' then c.[QTY] else 0 end)  [TON]
From [KHOHANG].[dbo].[DMHH] a
left join [KHOHANG].[dbo].[TONDAUKY] b on a.[MA_HANG]=b.[MA_HANG]
left join [KHOHANG].[dbo].[XN] c on a.[MA_HANG]=c.[MA_HANG]
Where (c.[MA_HANG] = N'MH0001' and c.[LOTNO] =N'L246357')
and (b.[MA_HANG]= N'MH0001' and b.[LOTNO]=N'L246357' and b.[KHO_LUU_TRU] like N'%A%')
Group by a.[MA_HANG],b.[KHO_LUU_TRU], c.[LOTNO]
Order by a.[MA_HANG]

Bài đã được tự động gộp: 7/10/20

Chỗ trống như hình là "A" hay trống vậy em?

View attachment 246957

Cảm ơn anh Hai Lúa đã quan tâm ạ.
Chỗ trống đó là "A" anh ạ, xin lỗi anh OT không kéo hết lên trên.
Vì OT sử dụng câu lệnh truy vấn để tìm theo từng mã và từng kho lên chỉ có một mã và một kho thôi ạ.

 

[Hai Lúa Miền Tây]

Dạ đây ạ, OT gửi bạn nhờ bạn kiểm tra giúp ạ.

Select a.[MA_HANG],b.[KHO_LUU_TRU],c.[LOTNO],
sum(b.[QTY]) [TDK],
sum(case when c.[KIEU]='N' then c.[QTY] else 0 end) [NHAP],
sum(case when c.[KIEU]='X' then c.[QTY] else 0 end) [XUAT],
sum(b.[QTY])+sum(case when c.[KIEU]='N' then c.[QTY] else 0 end)-sum(case when c.[KIEU]='X' then c.[QTY] else 0 end)  [TON]
From [KHOHANG].[dbo].[DMHH] a
left join [KHOHANG].[dbo].[TONDAUKY] b on a.[MA_HANG]=b.[MA_HANG]
left join [KHOHANG].[dbo].[XN] c on a.[MA_HANG]=c.[MA_HANG]
Where (c.[MA_HANG] = N'MH0001' and c.[LOTNO] =N'L246357')
and (b.[MA_HANG]= N'MH0001' and b.[LOTNO]=N'L246357' and b.[KHO_LUU_TRU] like N'%A%')
Group by a.[MA_HANG],b.[KHO_LUU_TRU], c.[LOTNO]
Order by a.[MA_HANG]

Bài đã được tự động gộp: 7/10/20

Cảm ơn anh Hai Lúa đã quan tâm ạ.
Chỗ trống đó là "A" anh ạ, xin lỗi anh OT không kéo hết lên trên.
Vì OT sử dụng câu lệnh truy vấn để tìm theo từng mã và từng kho lên chỉ có một mã và một kho thôi ạ.

Em thử code sau và biến tấu nhé.

Sub GomDL_HLMT()
    Dim strSQL As String
    strSQL = "SELECT MA_HANG, SO_LUONG AS TONDAUKY, KHO_LUU_TRU, 0 AS NHAP,0 AS XUAT, SO_LUONG AS TON FROM [TDK$] " & _
             "UNION ALL SELECT MA_HANG, 0, KHO_LUU_TRU, IIF(KIEU='N',SO_LUONG,0) AS NHAP,IIF(KIEU='X',SO_LUONG,0) AS XUAT, IIF(KIEU='N',SO_LUONG,0)-IIF(KIEU='X',SO_LUONG,0) FROM [NX$]"
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName)
        Sheet6.Range("A2").CopyFromRecordset .Execute("SELECT MA_HANG, SUM(TONDAUKY), KHO_LUU_TRU, SUM(NHAP), SUM(XUAT),SUM(TON) FROM (" & strSQL & ") GROUP BY MA_HANG, KHO_LUU_TRU")
    End With
  
End Sub

 

[Hai Lúa Miền Tây]

Dạ đây ạ, OT gửi bạn nhờ bạn kiểm tra giúp ạ.

Select a.[MA_HANG],b.[KHO_LUU_TRU],c.[LOTNO],
sum(b.[QTY]) [TDK],
sum(case when c.[KIEU]='N' then c.[QTY] else 0 end) [NHAP],
sum(case when c.[KIEU]='X' then c.[QTY] else 0 end) [XUAT],
sum(b.[QTY])+sum(case when c.[KIEU]='N' then c.[QTY] else 0 end)-sum(case when c.[KIEU]='X' then c.[QTY] else 0 end)  [TON]
From [KHOHANG].[dbo].[DMHH] a
left join [KHOHANG].[dbo].[TONDAUKY] b on a.[MA_HANG]=b.[MA_HANG]
left join [KHOHANG].[dbo].[XN] c on a.[MA_HANG]=c.[MA_HANG]
Where (c.[MA_HANG] = N'MH0001' and c.[LOTNO] =N'L246357')
and (b.[MA_HANG]= N'MH0001' and b.[LOTNO]=N'L246357' and b.[KHO_LUU_TRU] like N'%A%')
Group by a.[MA_HANG],b.[KHO_LUU_TRU], c.[LOTNO]
Order by a.[MA_HANG]

Bài đã được tự động gộp: 7/10/20

Cảm ơn anh Hai Lúa đã quan tâm ạ.
Chỗ trống đó là "A" anh ạ, xin lỗi anh OT không kéo hết lên trên.
Vì OT sử dụng câu lệnh truy vấn để tìm theo từng mã và từng kho lên chỉ có một mã và một kho thôi ạ.

Lưu ý dùng join trong trường hợp này sẽ không chính xác, bởi vì mỗi dòng phát sinh sẽ có 1 cái tồn đầu kỳ tương ứng với số dòng.

 

[ongke0711]

   ...
    strSQL = "SELECT MA_HANG, SO_LUONG AS TONDAUKY, KHO_LUU_TRU, 0 AS NHAP,0 AS XUAT, SO_LUONG AS TON FROM [TDK$] " & _
             "UNION ALL SELECT MA_HANG, 0, KHO_LUU_TRU, IIF(KIEU='N',SO_LUONG,0) AS NHAP,IIF(KIEU='X',SO_LUONG,0) AS XUAT,
..

Cùng suy nghĩ là dùng Union Query như bác HLMT. Đây là cách truyền thống bên Access hay làm để xử lý NXT.

 

[Hai Lúa Miền Tây]

Cùng suy nghĩ là dùng Union Query như bác HLMT.

Bài toán này dùng Union là hợp lý, khó có thể dùng join. Vì sao thì đã giải thích ở bài trên.

 

[Hoàng Nhật Phương]

Em thử code sau và biến tấu nhé.

Sub GomDL_HLMT()
    Dim strSQL As String
    strSQL = "SELECT MA_HANG, SO_LUONG AS TONDAUKY, KHO_LUU_TRU, 0 AS NHAP,0 AS XUAT, SO_LUONG AS TON FROM [TDK$] " & _
             "UNION ALL SELECT MA_HANG, 0, KHO_LUU_TRU, IIF(KIEU='N',SO_LUONG,0) AS NHAP,IIF(KIEU='X',SO_LUONG,0) AS XUAT, IIF(KIEU='N',SO_LUONG,0)-IIF(KIEU='X',SO_LUONG,0) FROM [NX$]"
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName)
        Sheet6.Range("A2").CopyFromRecordset .Execute("SELECT MA_HANG, SUM(TONDAUKY), KHO_LUU_TRU, SUM(NHAP), SUM(XUAT),SUM(TON) FROM (" & strSQL & ") GROUP BY MA_HANG, KHO_LUU_TRU")
    End With
 
End Sub

Anh Hai Lúa Miền Tây ơi câu lệnh này của anh OT thử trên Excel đúng ý mình rồi nhưng OT đang thử trong SQL và thấy anh không tham chiếu đến bảng DMHH
Anh có thể sửa thêm cho OT tham chiếu thêm đến bảng này nữa được không ạ, vì OT muốn dựa vào Mã hàng để lấy thêm các thông tin khác từ bảng này khi cần ạ.
Cảm ơn anh Hai Lúa Miền Tây

Bài đã được tự động gộp: 7/10/20

Cùng suy nghĩ là dùng Union Query như bác HLMT. Đây là cách truyền thống bên Access hay làm để xử lý NXT.

Hehe, OT cảm ơn anh ongke0711 ạ

 

[Hai Lúa Miền Tây]

Anh Hai Lúa Miền Tây ơi câu lệnh này của anh OT thử trên Excel đúng ý mình rồi nhưng OT đang thử trong SQL và thấy anh không tham chiếu đến bảng DMHH
Anh có thể sửa thêm cho OT tham chiếu thêm đến bảng này nữa được không ạ, vì OT muốn dựa vào Mã hàng để lấy thêm các thông tin khác từ bảng này khi cần ạ.
Cảm ơn anh Hai Lúa Miền Tây

Bài đã được tự động gộp: 7/10/20

Hehe, OT cảm ơn anh ongke0711 ạ

Em nên nghiên cứu thêm về các kiểu truy vấn nhé.

SELECT     A.MA_HANG,
           B.TEN_HANG,
           SUM(TONDAUKY),
           KHO_LUU_TRU,
           SUM(NHAP),
           SUM(XUAT),
           SUM(TON)
FROM       (" & strSQL & ") A
INNER JOIN [DMHH$] B
ON         A.MA_HANG=B.MA_HANG
GROUP BY   A.MA_HANG,
           KHO_LUU_TRU,
           B.TEN_HANG

 

[excel_lv1.5]

Dạ đây ạ, OT gửi bạn nhờ bạn kiểm tra giúp ạ.

Select a.[MA_HANG],a.[KHO_LUU_TRU],c.[LOTNO],
sum(b.[QTY]) [TDK],
sum(case when c.[KIEU]='N' then c.[QTY] else 0 end) [NHAP],
sum(case when c.[KIEU]='X' then c.[QTY] else 0 end) [XUAT],
sum(b.[QTY])+sum(case when c.[KIEU]='N' then c.[QTY] else 0 end)-sum(case when c.[KIEU]='X' then c.[QTY] else 0 end)  [TON]
From [KHOHANG].[dbo].[DMHH] a
left join [KHOHANG].[dbo].[TONDAUKY] b on a.[MA_HANG]=b.[MA_HANG]
left join [KHOHANG].[dbo].[XN] c on a.[MA_HANG]=c.[MA_HANG]
Where (c.[MA_HANG] = N'MH0001' and c.[LOTNO] =N'L246357')
and (b.[MA_HANG]= N'MH0001' and b.[LOTNO]=N'L246357' and b.[KHO_LUU_TRU] like N'%A%')
Group by a.[MA_HANG],b.[KHO_LUU_TRU], c.[LOTNO]
Order by a.[MA_HANG]

Bài đã được tự động gộp: 7/10/20

Cảm ơn anh Hai Lúa đã quan tâm ạ.
Chỗ trống đó là "A" anh ạ, xin lỗi anh OT không kéo hết lên trên.
Vì OT sử dụng câu lệnh truy vấn để tìm theo từng mã và từng kho lên chỉ có một mã và một kho thôi ạ.

Bạn gắn thêm [kho_luu_tru] và [lot_no] thì bảng danh mục bảng phải có 2 cột đó và điều kiện join cũng phải dùng thêm 2 điều kiện này, sài join cũng bình thường thôi

Select a.[MA_HANG],a.[KHO_LUU_TRU],a.[LOTNO],
sum(b.[QTY]) [TDK],
sum(case when c.[KIEU]='N' then c.[QTY] else 0 end) [NHAP],
sum(case when c.[KIEU]='X' then c.[QTY] else 0 end) [XUAT],
sum(b.[QTY])+sum(case when c.[KIEU]='N' then c.[QTY] else 0 end)-sum(case when c.[KIEU]='X' then c.[QTY] else 0 end)  [TON]
From [KHOHANG].[dbo].[DMHH] a
left join [KHOHANG].[dbo].[TONDAUKY] b on a.[MA_HANG]=b.[MA_HANG] and a.[kho_luu_tru]=b.[kho_luu_tru] and a.[lotno]=b.[lotno]
left join [KHOHANG].[dbo].[XN] c on a.[MA_HANG]=c.[MA_HANG]  and a.[kho_luu_tru]=c.[kho_luu_tru] and a.[lotno]=c.[lotno]
Where (c.[MA_HANG] = N'MH0001' and c.[LOTNO] =N'L246357')
and (b.[MA_HANG]= N'MH0001' and b.[LOTNO]=N'L246357' and b.[KHO_LUU_TRU] like N'%A%')
Group by a.[MA_HANG],b.[KHO_LUU_TRU], c.[LOTNO]
Order by a.[MA_HANG]

Trong trường hợp này thì không phải là cái Table Dim Dmhh nữa mà là một Table Distinct mới tạo ra từ table TD và NX theo 3 điều kiện [mahang], [kho_luu_tru] và [lotno], bảng dmhh chỉ có [mahang] sẽ không map được cả 3 điều kiện trên

 

[Hoàng Nhật Phương]

Bạn gắn thêm [kho_luu_tru] và [lot_no] thì bảng danh mục bảng phải có 2 cột đó và điều kiện join cũng phải dùng thêm 2 điều kiện này, sài join cũng bình thường thôi

Select a.[MA_HANG],a.[KHO_LUU_TRU],a.[LOTNO],
sum(b.[QTY]) [TDK],
sum(case when c.[KIEU]='N' then c.[QTY] else 0 end) [NHAP],
sum(case when c.[KIEU]='X' then c.[QTY] else 0 end) [XUAT],
sum(b.[QTY])+sum(case when c.[KIEU]='N' then c.[QTY] else 0 end)-sum(case when c.[KIEU]='X' then c.[QTY] else 0 end)  [TON]
From [KHOHANG].[dbo].[DMHH] a
left join [KHOHANG].[dbo].[TONDAUKY] b on a.[MA_HANG]=b.[MA_HANG] and a.[kho_luu_tru]=b.[kho_luu_tru] and a.[lotno]=b.[lotno]
left join [KHOHANG].[dbo].[XN] c on a.[MA_HANG]=c.[MA_HANG]  and a.[kho_luu_tru]=c.[kho_luu_tru] and a.[lotno]=c.[lotno]
Where (c.[MA_HANG] = N'MH0001' and c.[LOTNO] =N'L246357')
and (b.[MA_HANG]= N'MH0001' and b.[LOTNO]=N'L246357' and b.[KHO_LUU_TRU] like N'%A%')
Group by a.[MA_HANG],b.[KHO_LUU_TRU], c.[LOTNO]
Order by a.[MA_HANG]

Cảm ơn Bạn nhiều từ nãy giờ OT vẫn loay hoay chưa ứng dụng các câu truy vấn của anh Hai Lúa và Bạn vào thử trong SQL, hihi
Excel chạy ngon lành chắc là SQL cũng vậy thôi ạ,để OT thử thêm ạ.

 

[Hai Lúa Miền Tây]

Cảm ơn Bạn nhiều từ nãy giờ OT vẫn loay hoay chưa ứng dụng các câu truy vấn của anh Hai Lúa và Bạn vào thử trong SQL, hihi
Excel chạy ngon lành chắc là SQL cũng vậy thôi ạ,để OT thử thêm ạ.

Trong SQLServer không có hàm IIF vì vậy em phải chuyển từ IIF sang Case When nhé.

 

[Hoàng Nhật Phương]

Trong SQLServer không có hàm IIF vì vậy em phải chuyển từ IIF sang Case When nhé.

Úi zời,, OT loay hoay từ nãy giờ ạ
Vâng cảm ơn anh Hai Lúa ạ T_T

 

[excel_lv1.5]

Cảm ơn Bạn nhiều từ nãy giờ OT vẫn loay hoay chưa ứng dụng các câu truy vấn của anh Hai Lúa và Bạn vào thử trong SQL, hihi
Excel chạy ngon lành chắc là SQL cũng vậy thôi ạ,để OT thử thêm ạ.

Dùng Union chắc ổn hơn đấy bạn, còn dùng cách tôi bạn phải tạo thêm một table mới thay cho bảng DMHH , vì trong trường hợp này bảng DMHH không sử dụng được nữa, nếu dùng SQL server bạn có thể thử cách pivot

Bài đã được tự động gộp: 7/10/20

Trong SQLServer không có hàm IIF vì vậy em phải chuyển từ IIF sang Case When nhé.

Vẫn có hàm IIF nhe bạn

 

[Hoàng Nhật Phương]

Dùng Union chắc ổn hơn đấy bạn, còn dùng cách tôi bạn phải tạo thêm một table mới thay cho bảng DMHH , vì trong trường hợp này bảng DMHH không sử dụng được nữa, nếu dùng SQL server bạn có thể thử cách pivot

Cảm ơn Bạn nhiều,
OT đã thử câu truy vấn của bạn thấy không ra kết quả gì ngoài các dòng tiêu đề ạ.
left join [KHOHANG].[dbo].[TONDAUKY] b on a.[MA_HANG]=b.[MA_HANG] and a.[kho_luu_tru]=b.[kho_luu_tru] and a.[lotno]=b.[lotno]
left join [KHOHANG].[dbo].[XN] c on a.[MA_HANG]=c.[MA_HANG] and a.[kho_luu_tru]=c.[kho_luu_tru] and a.[lotno]=c.[lotno]
OT thấy các chỗ tô đậm nó khó hiểu thế nào ấy ạ, vì trong bảng a(DMHH) không có trường 'kho_luu_tru' và 'lotno' bạn ạ.
Khi OT xóa các dòng này đi thì số liệu có nhưng chênh lên nhiều như OT đã thông tin bài trước ạ.
Để OT tìm hiểu thêm ạ, cảm ơn bạn đã cố gắng giúp đỡ OT.

 

[Hai Lúa Miền Tây]

Vẫn có hàm IIF nhe bạn

Nó có từ bản 2012 nhé bạn. Những bản trước đó không có hàm này.

 

[Hoàng Nhật Phương]

Nó có từ bản 2012 nhé bạn. Những bản trước đó không có hàm này.

Của OT vừa hỏi anh kỹ thuật anh ấy bảo SQL này là phiên bản 2016 ạ. hehe

 

[Hai Lúa Miền Tây]

Của OT vừa hỏi anh kỹ thuật anh ấy bảo SQL này là phiên bản 2016 ạ. hehe

Em hỏi kỹ lại anh IT đó coi phiên bản 2012 có chưa nhé.

 

[Hoàng Nhật Phương]

Em hỏi kỹ lại anh IT đó coi phiên bản 2012 có chưa nhé.

Anh ý không có chuyên môn về những câu lệnh select kiểu này đâu anh, nếu anh ấy biết OT không phải vất vả nhiều về vấn đề này rồi anh ạ.
Anh ấy chỉ cho phép OT quyền vào rồi cũng tạo backup data cho OT nghịch trên DB sau khi backup thôi anh ạ, chứ sờ vào DB thật anh ấy mắng ạ.

 

[Hai Lúa Miền Tây]

Anh ý không có chuyên môn về những câu lệnh select kiểu này đâu anh, nếu anh ấy biết OT không phải vất vả nhiều về vấn đề này rồi anh ạ.
Anh ấy chỉ cho phép OT quyền vào rồi cũng tạo backup data cho OT nghịch trên DB sau khi backup thôi anh ạ, chứ sờ vào DB thật anh ấy mắng ạ.

Ý anh là phiên bản 2012 về sau mới có hàm IIF. Những phiên bản trước đó chưa có hàm này. Hay ý em là khác? Ý em là em đang dùng SQLServer 2016 mà không dùng được đoạn truy vấn trên?

 

[excel_lv1.5]

Cảm ơn Bạn nhiều,
OT đã thử câu truy vấn của bạn thấy không ra kết quả gì ngoài các dòng tiêu đề ạ.
left join [KHOHANG].[dbo].[TONDAUKY] b on a.[MA_HANG]=b.[MA_HANG] and a.[kho_luu_tru]=b.[kho_luu_tru] and a.[lotno]=b.[lotno]
left join [KHOHANG].[dbo].[XN] c on a.[MA_HANG]=c.[MA_HANG] and a.[kho_luu_tru]=c.[kho_luu_tru] and a.[lotno]=c.[lotno]
OT thấy các chỗ tô đậm nó khó hiểu thế nào ấy ạ, vì trong bảng a(DMHH) không có trường 'kho_luu_tru' và 'lotno' bạn ạ.
Khi OT xóa các dòng này đi thì số liệu có nhưng chênh lên nhiều như OT đã thông tin bài trước ạ.
Để OT tìm hiểu thêm ạ, cảm ơn bạn đã cố gắng giúp đỡ OT.

join nó giống hàm lookup trong excel cho phép tìm kiếm nhiều điều kiện
Thì tôi nói là bạn làm NXT theo 3 yếu tố [mahang],[kho_luu_tru],[lotno] nên bảng DMHH sẽ không sử dụng được do nó chỉ có [mahang], nên bạn dùng cách union thì sẽ ổn hơn.
Còn vẫn muốn đi theo hướng đó thì bạn có thể thêm 1 đoạn tạo ra một bảng table DMHH mới như vầy, rồi chạy lại đoạn code trên (hoặc lồng vào đoạn code trên cũng được)

select DISTINCT [ma_hang],[kho_luu_tru],[lotno]
into #dmhh
from
(
    select [ma_hang],[kho_luu_tru],[lotno] from [TDK]
    union all
    select [ma_hang],[kho_luu_tru],[lotno] from [NX]
) a

 

[Hoàng Nhật Phương]

Ý anh là phiên bản 2012 về sau mới có hàm IIF. Những phiên bản trước đó chưa có hàm này. Hay ý em là khác? Ý em là em đang dùng SQLServer 2016 mà không dùng được đoạn truy vấn trên?

À ý em là phiên bản SQL của OT là sau 2012 rồi nên OT yên tâm không phải sửa gì câu lệnh truy vấn của anh nữa ạ.
OT chạy câu lệnh truy vấn của anh rồi và nó không báo lỗi gì có nghĩa là iff có thể sử dụng được ạ, nhưng kết quả chưa đúng chắc là sai sót chỗ nào thôi ạ do OT bổ sung thêm select kèm 1 thêm trường, OT đang mò mẫm.
Hic cả ngày hôm nay không làm ăn gì được ngoài test cái vấn đề này anh ạ, công việc ùn hôm sau làm tiếp cũng được anh ạ hehe

Bài đã được tự động gộp: 7/10/20

join nó giống hàm lookup trong excel cho phép tìm kiếm nhiều điều kiện
Thì tôi nói là bạn làm NXT theo 3 yếu tố [mahang],[kho_luu_tru],[lotno] nên bảng DMHH sẽ không sử dụng được do nó chỉ có [mahang], nên bạn dùng cách union thì sẽ ổn hơn.
Còn vẫn muốn đi theo hướng đó thì bạn có thể thêm 1 đoạn tạo ra một bảng table DMHH mới như vầy, rồi chạy lại đoạn code trên (hoặc lồng vào đoạn code trên cũng được)

select DISTINCT [ma_hang],[kho_luu_tru],[lotno]
into #dmhh
from
(
    select [ma_hang],[kho_luu_tru],[lotno] from [TDK]
    union all
    select [ma_hang],[kho_luu_tru],[lotno] from [NX]
) a

Dạ vâng cảm ơn bạn nhiều, OT sẽ test hết các trường hợp rồi thông tin lại ạ.