Truy vấn bằng SQL dữ liệu từ 2 bảng: bảng tồn và bảng nhập xuất (1 người xem)

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

Xin chào các bạn, OT có một vấn đề sau chưa giải quyết được lên đưa lên đây nhờ tất cả mọi người cùng xem và giúp đỡ.
Cụ thể có 2 bảng dữ liệu 'Inventory' và 'Data':




Nhờ các bạn giúp đỡ câu lệnh SQL để lấy dữ liệu từ 2 bảng trên trả về kết quả sau:




Xin trân trọng cảm ơn

 

[xì tin]

Xin chào các bạn, OT có một vấn đề sau chưa giải quyết được lên đưa lên đây nhờ tất cả mọi người cùng xem và giúp đỡ.
Cụ thể có 2 bảng dữ liệu 'Inventory' và 'Data':

View attachment 291800
View attachment 291801

Nhờ các bạn giúp đỡ câu lệnh SQL để lấy dữ liệu từ 2 bảng trên trả về kết quả sau:


View attachment 291802

Xin trân trọng cảm ơn

Bạn phải miêu tả là làm sao ra được số lượng StockIn và StockOut nha hôn.

 

[ptm0412]

Nếu nói về SQL:
- Khóa liên kết = Item & LotNo & StockNo
- Select If (StockType = "Out" Or StockType = "Mov") and StockNo = "Kho-004" then Quantity else 0 As StockOut
thêm If StockType = "In" and StockNo = "Kho-004" Or Or StockType = "Mov" and StockNoTo = "Kho-004" then Quantity else 0 As Stockin
- Where Item = "MH002"

Đại khái vậy

 

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

Cảm ơn chú Mỹ và bạn @xì tin đã quan tâm và góp ý.
Chú Mỹ giúp con câu lệnh hoàn chỉnh với, con loay hoay mãi rồi nhưng chưa được.
Nếu là VBA thì code sẽ như thế này ạ:

Option Explicit

Sub testCalcInventory()

    Dim dic As Object, sheet As Worksheet
    Dim inventory As Variant, scan As Variant, result As Variant, Key As Variant
    Dim sPart As String, sLot As String, sTockNo As String, sTockNoTo As String, str As String, sTockNoSearch As String
    Dim i As Long, j As Long, r As Long, u As Long, k As Long
    Dim quantity As Double
    Dim c As Integer, style As String
    
    Const sDELIM As String = "|"
    
    Set sheet = ThisWorkbook.ActiveSheet
    inventory = sheet.Range("A3:E6").Value
    scan = sheet.Range("G3:O29").Value
    
    sTockNoSearch = "*KHO_004*"
    
    If IsArray(inventory) Then r = UBound(inventory, 1)
    If IsArray(scan) Then r = r + UBound(scan, 1)
    
    If (r = 0) Then
        Exit Sub
    End If
    
    If Not IsArray(scan) Then
        GoTo Iventory_
    End If
    
Scaner_:
    
    Set dic = CreateObject("Scripting.Dictionary")
    c = 8:     ReDim result(1 To r * 2, 1 To c)
    
    For i = LBound(scan, 1) To UBound(scan, 1)
        
        style = scan(i, 1)
        sPart = scan(i, 2)
        sTockNoTo = scan(i, 3)
        sTockNo = scan(i, 4)
        sLot = scan(i, 7)
        quantity = scan(i, 8)

        str = Join(Array(sPart, sTockNoTo, sLot), sDELIM)
        If Not dic.Exists(str) And (style = "MOV") Then
            If UCase(sTockNoTo) Like UCase(sTockNoSearch) Then
                k = k + 1
                dic.Add str, k
                result(k, 1) = k
                result(k, 2) = sPart
                result(k, 3) = sLot
                result(k, 4) = sTockNoTo
            End If
        End If
        
        Key = Join(Array(sPart, sTockNo, sLot), sDELIM)
        If Not dic.Exists(Key) Then
            If UCase(sTockNo) Like UCase(sTockNoSearch) Then
                k = k + 1
                dic.Add Key, k
                result(k, 1) = k
                result(k, 2) = sPart
                result(k, 3) = sLot
                result(k, 4) = sTockNo
            End If
        End If
        
        u = dic.Item(str):  r = dic.Item(Key)
        
        Select Case style
           Case "IN" 'NHAP KHO
                If (r > 0) Then result(r, 6) = result(r, 6) + quantity
                If (r > 0) Then result(r, 8) = result(r, 8) + quantity
           Case "OUT" 'XUAT KHO
                If (r > 0) Then result(r, 7) = result(r, 7) + quantity
                If (r > 0) Then result(r, 8) = result(r, 8) - quantity
           Case "MOV" 'CHUYEN KHO
                If (u > 0) Then result(u, 6) = result(u, 6) + quantity
                If (u > 0) Then result(u, 8) = result(u, 8) + quantity
                If (r > 0) Then result(r, 7) = result(r, 7) + quantity
                If (r > 0) Then result(r, 8) = result(r, 8) - quantity
        End Select
                
    Next i

    If Not IsArray(inventory) Then
        GoTo Result_
    End If
    
Iventory_:
    For i = LBound(inventory, 1) To UBound(inventory, 1)
        sPart = inventory(i, 1)
        sTockNo = inventory(i, 2)
        sLot = inventory(i, 3)
        quantity = inventory(i, 4)
        Key = Join(Array(sPart, sTockNo, sLot), sDELIM)
        str = Join(Array(sDELIM, sPart, sTockNo, sLot), sDELIM)
        If UCase(sTockNo) Like UCase(sTockNoSearch) Then
            If Not dic.Exists(Key) And (quantity > 0) Then
                k = k + 1
                dic.Add Key, k
                result(k, 1) = k
                result(k, 2) = sPart
                result(k, 3) = sLot
                result(k, 4) = sTockNo
                result(k, 5) = quantity
                result(k, 6) = quantity
            Else
                If Not dic.Exists(str) And (quantity > 0) Then
                    dic.Add str, quantity:  r = dic.Item(Key)
                    result(r, 5) = quantity
                    result(r, 8) = result(r, 5) + result(r, 6) - result(r, 7)
                End If
            End If
        End If
    Next i
    
Result_:
    If (k > 0) Then sheet.Range("P13").Resize(k, c).Value = result
    
End Sub

 

[HieuCD]

Xin chào các bạn, OT có một vấn đề sau chưa giải quyết được lên đưa lên đây nhờ tất cả mọi người cùng xem và giúp đỡ.
Cụ thể có 2 bảng dữ liệu 'Inventory' và 'Data':

View attachment 291800
View attachment 291801

Nhờ các bạn giúp đỡ câu lệnh SQL để lấy dữ liệu từ 2 bảng trên trả về kết quả sau:


View attachment 291802

Xin trân trọng cảm ơn

Dữ liệu và kết quả trong 1 file dùng dic nhanh và dể viết hơn SQL nhiều

 

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

Dữ liệu và kết quả trong 1 file dùng dic nhanh và dể viết hơn SQL nhiều

Con chào bác Hiếu,
Dạ con mô tả vậy thôi bác, dữ liệu là lấy trực tiếp từ SQL ra đó bác. Con muốn thay vì lấy về sau đó mới xử lý thì sẽ xử lý luôn bằng câu lệnh SQL bác ạ. Với lại con cũng muốn tìm hiểu về câu lệnh SQL với trường hợp này ạ, vì trong thực tế con gặp nhiều.
Cảm ơn bác đã góp ý ạ.

 

[HieuCD]

Con chào bác Hiếu,
Dạ con mô tả vậy thôi bác, dữ liệu là lấy trực tiếp từ SQL ra đó bác. Con muốn thay vì lấy về sau đó mới xử lý thì sẽ xử lý luôn bằng câu lệnh SQL bác ạ. Với lại con cũng muốn tìm hiểu về câu lệnh SQL với trường hợp này ạ, vì trong thực tế con gặp nhiều.
Cảm ơn bác đã góp ý ạ.

Gặp ca phức tạp bạn nên dùng nhiều query, trường hợp nầy cần 2 query lấy dữ liệu từ 2 bảng và 1 query lấy kết quả từ 2 query trên, SQL được đơn giản hóa

 

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

Gặp ca phức tạp bạn nên dùng nhiều query, trường hợp nầy cần 2 query lấy dữ liệu từ 2 bảng và 1 query lấy kết quả từ 2 query trên, SQL được đơn giản hóa

Dạ dùng nhiều query cũng được bác con cũng thường làm như vậy, khi kết nối đến CSDL thì sẽ cho chạy hết một lượt ạ.
Vấn đề là con cũng đang chưa biết câu query này như thế nào bác:

1 query lấy kết quả từ 2 query trên

Nhờ bác và mọi người chỉ giúp ạ.

 

[xì tin]

Nếu nói về SQL:
- Khóa liên kết = Item & LotNo & StockNo
- Select If (StockType = "Out" Or StockType = "Mov") and StockNo = "Kho-004" then Quantity else 0 As StockOut
thêm If StockType = "In" and StockNo = "Kho-004" Or Or StockType = "Mov" and StockNoTo = "Kho-004" then Quantity else 0 As Stockin
- Where Item = "MH002"

Đại khái vậy

Trường hợp này ta có thể không cần dùng khoá như bạn đề cập.

 

[VetMini]

Table1 là Master File. Chứa dữ liệu căn bản của Inventory (hàng hóa)
Table2 là Transaction File. Chứa các phát sinh cho Inventory (xuất nhập,...)
Đối với mọi CSDL, phân biệt loại bảng là điều quan trọng, cần phải nhớ rõ.

Bài này cần khá nhiều bảng dữ liệu tạm.
1. Lọc dữ liệu theo mã inventory
(Select * From Table2 Where Item = 'MH002') As Table22.
2. Lọc theo IN
(Select *, StockNo As PosNo, Quantity As QtyIN, 0 As QtyOUT From Table22 Where StockType = 'IN')
Lọc theo OUT
(Select *, StockNo As PosNo, 0 As QtyIN, Quantity As QtyOUT From Table22 Where StockType = 'OUT')
Lọc theo MOV (in)
(Select *, StockNoTo As PosNo, Quantity As QtyIN, 0 As QtyOUT From Table22 Where StockType = 'MOV')
Lọc theo MOV (out)
(Select *, StockNo As PosNo, 0 As QtyIN, Quantity As QtyOUT From Table22 Where StockType = 'MOV')

UNION ALL 4 cái lọc trên, bạn sẽ được một bảng transaction (phát sinh) mới cho Item chọn lựa và IN/OUT đã phân biệt rõ.
Công việc kế tiếp là GROUP chúng lại theo ITEM, POSNO, LOTNO; và tổng các Qty's
Công việc bây giờ của bạn chỉ là JOIN nó (Item và LotNo, POSNO với STOCKNO) với Table1 để lấy thêm dữ liệu (tồn đầu)

Đó là cách làm căn bản để lọc lại Transactions với nhiều kiểu (IN/OUT/MOV)
Nó rắc rối bởi vì cách thiết kế hai bảng Master và Transaction của bạn không đạt chuẩn.

Cách làm gọn hơn là theo ý của bài #227, các Transactions chỉ lọc một lần và dùng lô gic IF-THEN-ELSE để phân biệt cách đối xử khi gặp các loại StockType khác nhau (lưu ý là bài của bạn dùng từ Stocktype là sai, tên đúng thì phải là TransType)

Tuy nhiên, bài #227 trên chỉ gợi ý. SQL của Access không hỗ trợ lệnh If-Then-Else hay Case cho nên bạn phải dùng IIF hoặc Switch.
Lưu ý là Table2 của bạn thiết kế rất kém cho nên việc xử lý loại "MOV" rất phức tạp. Làm cách nào để đổi một MOV thành 1 OUT (dễ) cộng 1 IN (rất khó).

 

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

Table1 là Master File. Chứa dữ liệu căn bản của Inventory (hàng hóa)
Table2 là Transaction File. Chứa các phát sinh cho Inventory (xuất nhập,...)
Đối với mọi CSDL, phân biệt loại bảng là điều quan trọng, cần phải nhớ rõ.

Bài này cần khá nhiều bảng dữ liệu tạm.
1. Lọc dữ liệu theo mã inventory
(Select * From Table2 Where Item = 'MH002') As Table22.
2. Lọc theo IN
(Select *, StockNo As PosNo, Quantity As QtyIN, 0 As QtyOUT From Table22 Where StockType = 'IN')
Lọc theo OUT
(Select *, StockNo As PosNo, 0 As QtyIN, Quantity As QtyOUT From Table22 Where StockType = 'OUT')
Lọc theo MOV (in)
(Select *, StockNoTo As PosNo, Quantity As QtyIN, 0 As QtyOUT From Table22 Where StockType = 'MOV')
Lọc theo MOV (out)
(Select *, StockNo As PosNo, 0 As QtyIN, Quantity As QtyOUT From Table22 Where StockType = 'MOV')

UNION ALL 4 cái lọc trên, bạn sẽ được một bảng transaction (phát sinh) mới cho Item chọn lựa và IN/OUT đã phân biệt rõ.
Công việc kế tiếp là GROUP chúng lại theo ITEM, POSNO, LOTNO; và tổng các Qty's
Công việc bây giờ của bạn chỉ là JOIN nó (Item và LotNo, POSNO với STOCKNO) với Table1 để lấy thêm dữ liệu (tồn đầu)

Đó là cách làm căn bản để lọc lại Transactions với nhiều kiểu (IN/OUT/MOV)
Nó rắc rối bởi vì cách thiết kế hai bảng Master và Transaction của bạn không đạt chuẩn.

Cách làm gọn hơn là theo ý của bài #227, các Transactions chỉ lọc một lần và dùng lô gic IF-THEN-ELSE để phân biệt cách đối xử khi gặp các loại StockType khác nhau (lưu ý là bài của bạn dùng từ Stocktype là sai, tên đúng thì phải là TransType)

Tuy nhiên, bài #227 trên chỉ gợi ý. SQL của Access không hỗ trợ lệnh If-Then-Else hay Case cho nên bạn phải dùng IIF hoặc Switch.
Lưu ý là Table2 của bạn thiết kế rất kém cho nên việc xử lý loại "MOV" rất phức tạp. Làm cách nào để đổi một MOV thành 1 OUT (dễ) cộng 1 IN (rất khó).

Con cảm ơn bác VetMini nhiều, một gợi ý rất cơ bản và logic con sẽ thử xem con có làm được không ạ.

 

[xì tin]

Table1 là Master File. Chứa dữ liệu căn bản của Inventory (hàng hóa)
Table2 là Transaction File. Chứa các phát sinh cho Inventory (xuất nhập,...)
Đối với mọi CSDL, phân biệt loại bảng là điều quan trọng, cần phải nhớ rõ.

Bài này cần khá nhiều bảng dữ liệu tạm.
1. Lọc dữ liệu theo mã inventory
(Select * From Table2 Where Item = 'MH002') As Table22.
2. Lọc theo IN
(Select *, StockNo As PosNo, Quantity As QtyIN, 0 As QtyOUT From Table22 Where StockType = 'IN')
Lọc theo OUT
(Select *, StockNo As PosNo, 0 As QtyIN, Quantity As QtyOUT From Table22 Where StockType = 'OUT')
Lọc theo MOV (in)
(Select *, StockNoTo As PosNo, Quantity As QtyIN, 0 As QtyOUT From Table22 Where StockType = 'MOV')
Lọc theo MOV (out)
(Select *, StockNo As PosNo, 0 As QtyIN, Quantity As QtyOUT From Table22 Where StockType = 'MOV')

UNION ALL 4 cái lọc trên, bạn sẽ được một bảng transaction (phát sinh) mới cho Item chọn lựa và IN/OUT đã phân biệt rõ.
Công việc kế tiếp là GROUP chúng lại theo ITEM, POSNO, LOTNO; và tổng các Qty's
Công việc bây giờ của bạn chỉ là JOIN nó (Item và LotNo, POSNO với STOCKNO) với Table1 để lấy thêm dữ liệu (tồn đầu)

Đó là cách làm căn bản để lọc lại Transactions với nhiều kiểu (IN/OUT/MOV)
Nó rắc rối bởi vì cách thiết kế hai bảng Master và Transaction của bạn không đạt chuẩn.

Cách làm gọn hơn là theo ý của bài #227, các Transactions chỉ lọc một lần và dùng lô gic IF-THEN-ELSE để phân biệt cách đối xử khi gặp các loại StockType khác nhau (lưu ý là bài của bạn dùng từ Stocktype là sai, tên đúng thì phải là TransType)

Tuy nhiên, bài #227 trên chỉ gợi ý. SQL của Access không hỗ trợ lệnh If-Then-Else hay Case cho nên bạn phải dùng IIF hoặc Switch.
Lưu ý là Table2 của bạn thiết kế rất kém cho nên việc xử lý loại "MOV" rất phức tạp. Làm cách nào để đổi một MOV thành 1 OUT (dễ) cộng 1 IN (rất khó).

Trường hợp này ta nên thoải mái và thả lỏng nó ra chút nha bạn.
@Hoàng Nhật Phương:
Kết quả sẽ đúng yêu cầu nhưng thứ tự không được như ý bạn. Bạn có chịu không?

 

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

Trường hợp này ta nên thoải mái và thả lỏng nó ra chút nha bạn.
@Hoàng Nhật Phương:
Kết quả sẽ đúng yêu cầu nhưng thứ tự không được như ý bạn. Bạn có chịu không?

View attachment 291823

Dạ được ạ, chỉ cần ra kết quả đúng là đủ rồi ạ.
Còn thư tự theo kết quả mẫu không phải vấn đề,nhờ bạn giúp đỡ ạ.
Cảm ơn bạn nhiều.

 

[xì tin]

Dạ được ạ, chỉ cần ra kết quả đúng là đủ rồi ạ.
Còn thư tự theo kết quả mẫu không phải vấn đề,nhờ bạn giúp đỡ ạ.
Cảm ơn bạn nhiều.

Với đoạn truy vấn sau sẽ ra kết quả. Tuy nhiên sẽ còn cách khác tối ưu hơn.

SELECT ITEM,
       LOTNO,
       STOCKNO,
       Sum(OPENINGSTOCK),
       Sum(STOCKIN),
       Sum(STOCKOUT),
       Sum(OPENINGSTOCK) + Sum(STOCKIN) - Sum(STOCKOUT)
FROM   (SELECT ITEM,
               LOTNO,
               STOCKNO,
               0                                  AS OpeningStock,
               Iif(STOCKTYPE = 'IN', QUANTITY, 0) AS StockIn,
               Iif(STOCKTYPE = 'IN', 0, QUANTITY) AS StockOut
        FROM   [NXT$G2:O29]
        UNION ALL
        SELECT ITEM,
               LOTNO,
               STOCKNOTO,
               0,
               QUANTITY,
               0
        FROM   [NXT$G2:O29]
        WHERE  STOCKTYPE LIKE 'MOV'
        UNION ALL
        SELECT ITEM,
               LOTNO,
               STOCKNO,
               QUANTITY,
               0,
               0
        FROM   [NXT$A2:E6])
WHERE  STOCKNO LIKE 'KHO_004'
GROUP  BY ITEM,
          LOTNO,
          STOCKNO

 

[VetMini]

Trường hợp này ta nên thoải mái và thả lỏng nó ra chút nha bạn.

Tôi không muốn nói chuyện về cái này. Thoải mái hya khong chỉ là chủ quan của mỗi người.

@Hoàng Nhật Phương:
Kết quả sẽ đúng yêu cầu nhưng thứ tự không được như ý bạn. Bạn có chịu không?
...

Nếu đã tới kết quả rồi thì chỉ cần sort theo 12 ký tự đầu của LotNo (descending) và các ký tự còn lại (ascending)

 

[xì tin]

Tôi không muốn nói chuyện về cái này. Thoải mái hya khong chỉ là chủ quan của mỗi người.


Nếu đã tới kết quả rồi thì chỉ cần sort theo 12 ký tự đầu của LotNo (descending) và các ký tự còn lại (ascending)

Nếu dùng câu "Suy nghĩ thoáng hơn" thì hợp lý hơn. Như tôi đã nói, thứ tự sẽ không như mong muốn, Bạn hướng dẫn như trên chưa đạt 100% yêu cầu. Dĩ nhiên ta có cách sắp xếp theo thứ tự nhưng phức tạp không đáng.

 

[ptm0412]

Trường hợp này ta có thể không cần dùng khoá như bạn đề cập.

Bạn chưa thấy dữ liệu gốc bao giờ.
Bảng 1: Dữ liệu tồn kho bao giờ cũng có: Nhiều kho, nhiều mặt hàng, mỗi kho nhiều mặt hàng, mỗi mặt hàng nhiều kho, mỗi mặt hàng nhiều lot nhập về.
Bảng 2: dữ liệu phát sinh (hàng ngày) cũng nhiều dạng như vậy và nhân lên nhiều ngày.
Không có trường khóa tạo quan hệ là chết chắc.
Mấu chốt CSDL bắt buộc phải vậy chứ phát sinh nhập xuất lot này, kho này mà lấy tồn đầu kho khác, lot khác là báo cáo vứt đi ngay.

Tuy nhiên, bài #227 trên chỉ gợi ý.

Đúng vậy, tôi chỉ gợi ý. Đã biết SQL, hỏi trong chủ đề SQL, thì đáng lẽ chỉ gợi ý vậy thôi là làm được.

 

[xì tin]

Bạn chưa thấy dữ liệu gốc bao giờ.
Bảng 1: Dữ liệu tồn kho bao giờ cũng có: Nhiều kho, nhiều mặt hàng, mỗi kho nhiều mặt hàng, mỗi mặt hàng nhiều kho, mỗi mặt hàng nhiều lot nhập về.
Bảng 2: dữ liệu phát sinh (hàng ngày) cũng nhiều dạng như vậy và nhân lên nhiều ngày.
Không có trường khóa tạo quan hệ là chết chắc.
Mấu chốt CSDL bắt buộc phải vậy chứ phát sinh nhập xuất lot này, kho này mà lấy tồn đầu kho khác, lot khác là báo cáo vứt đi ngay.

Đúng vậy, tôi chỉ gợi ý. Đã biết SQL, hỏi trong chủ đề SQL, thì đáng lẽ chỉ gợi ý vậy thôi là làm được.

Vậy cái Group by nó không có ý nghĩa gì trong trường hợp này sao bạn? Kho thì đã Group theo mã kho rồi. Có nhiều kho mà có cùng mã hàng thì đâu ảnh hưởng gì phải không nào.

 

[ptm0412]

Vậy cái Group by nó không có ý nghĩa gì trong trường hợp này sao bạn? Kho thì đã Group theo mã kho rồi. Có nhiều kho mà có cùng mã hàng thì đâu ảnh hưởng gì phải không nào.

Group by theo tôi chỉ là biện pháp chữa cháy. Mà thôi, tôi nói thừa rồi. Thừa với người giỏi như bạn, còn đối với dữ liệu khác, người khác thì ... chuyện gì xảy ra nếu có 1 mặt hàng tồn kho không nhập xuất, hoặc 1 mặt hàng không tồn kho nhưng có nhập xuất?

 

[xì tin]

Group by theo tôi chỉ là biện pháp chữa cháy. Mà thôi, tôi nói thừa rồi. Thừa với người giỏi như bạn, còn đối với dữ liệu khác, người khác thì ... chuyện gì xảy ra nếu có 1 mặt hàng tồn kho không nhập xuất, hoặc 1 mặt hàng không tồn kho nhưng có nhập xuất?

Hi hi!!! Có ràng buộc dữ liệu là lúc nhập liệu á bạn, ở đây là xử lý dữ liệu thô đã có sẵn. Đúng không bạn?

 

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

Với đoạn truy vấn sau sẽ ra kết quả. Tuy nhiên sẽ còn cách khác tối ưu hơn.

SELECT ITEM,
       LOTNO,
       STOCKNO,
       Sum(OPENINGSTOCK),
       Sum(STOCKIN),
       Sum(STOCKOUT),
       Sum(OPENINGSTOCK) + Sum(STOCKIN) - Sum(STOCKOUT)
FROM   (SELECT ITEM,
               LOTNO,
               STOCKNO,
               0                                  AS OpeningStock,
               Iif(STOCKTYPE = 'IN', QUANTITY, 0) AS StockIn,
               Iif(STOCKTYPE = 'IN', 0, QUANTITY) AS StockOut
        FROM   [NXT$G2:O29]
        UNION ALL
        SELECT ITEM,
               LOTNO,
               STOCKNOTO,
               0,
               QUANTITY,
               0
        FROM   [NXT$G2:O29]
        WHERE  STOCKTYPE LIKE 'MOV'
        UNION ALL
        SELECT ITEM,
               LOTNO,
               STOCKNO,
               QUANTITY,
               0,
               0
        FROM   [NXT$A2:E6])
WHERE  STOCKNO LIKE 'KHO_004'
GROUP  BY ITEM,
          LOTNO,
          STOCKNO

Cảm ơn bạn đã giúp đỡ,
OT thử trường hợp này kết quả thấy khác với kết quả mong muốn.
Nhờ bạn kiểm tra giúp ạ.

 

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

Cảm ơn bạn đã giúp đỡ,
OT thử trường hợp này kết quả thấy khác với kết quả mong muốn.
Nhờ bạn kiểm tra giúp ạ.

Xin lỗi OT gửi lại dữ liệu chỉnh sửa ạ:
Xóa bớt một số ngày ở bảng xuất nhập không không trong phạm vi.

 

[ptm0412]

Hi hi!!! Có ràng buộc dữ liệu là lúc nhập liệu á bạn, ở đây là xử lý dữ liệu thô đã có sẵn. Đúng không bạn?

Tôi nói chung về mặt lý thuyết chứ không nói file cụ thể hay dữ liệu cụ thể.
Thực ra bảng 1 trong file trên đây cũng chỉ là file master thứ cấp, chứ không phải master gốc. Master gốc là danh mục mặt hàng chỉ có Mã, tên, đơn vị tính, mô tả thuộc tính. Hết. Mỗi mặt hàng chỉ xuất hiện duy nhất 1 lần.
Bảng master thứ cấp nếu là bảng số tồn như bảng 1 thì 1 mặt hàng có thể xuất hiện nhiều lần: nhiều lot (nhiều lần nhập), tồn ở nhiều kho, thậm chí tồn ở nhiều lần kiểm kê (cột InventoryDate khác nhau).
Vậy bảng master thứ cấp không phải lúc nào cũng có trường khóa để có quan hệ 1 nhiều với bảng khác, chỉ có bảng master gốc mới có thể. Tác giả câu hỏi cũng có thể chưa lường tới vụ này.

 

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

Tôi nói chung về mặt lý thuyết chứ không nói file cụ thể hay dữ liệu cụ thể.
Thực ra bảng 1 trong file trên đây cũng chỉ là file master thứ cấp, chứ không phải master gốc. Master gốc là danh mục mặt hàng chỉ có Mã, tên, đơn vị tính, mô tả thuộc tính. Hết. Mỗi mặt hàng chỉ xuất hiện duy nhất 1 lần.
Bảng master thứ cấp nếu là bảng số tồn như bảng 1 thì 1 mặt hàng có thể xuất hiện nhiều lần: nhiều lot (nhiều lần nhập), tồn ở nhiều kho, thậm chí tồn ở nhiều lần kiểm kê (cột InventoryDate khác nhau).
Vậy bảng master thứ cấp không phải lúc nào cũng có trường khóa để có quan hệ 1 nhiều với bảng khác, chỉ có bảng master gốc mới có thể. Tác giả câu hỏi cũng có thể chưa lường tới vụ này.

Đúng vậy chú ơi,các item xuất hiện bảng 2 chưa chắc đã có trong bảng 1 và cũng có thể có rất nhiều trong bảng 1.
Dữ liệu master ở bảng khác và đầy đủ các thông tin , mỗi item là duy nhất như chú nêu đó ạ. Các item phải được khai báo ở đây thì mới có dữ liệu ở bảng 1 và 2 được.
Nhưng vì chỉ đơn thuần tính toán nhập xuất tồn theo Item,Lot,Kho không tham chiếu gì đến thông tin khác đến bảng master nên con không đề cập.

Ủa mà chú đổi màu nick rồi ạ.. con chúc mừng chú nha.

 

[ptm0412]

Nhưng vì chỉ đơn thuần tính toán nhập xuất tồn theo Item,Lot,Kho không tham chiếu gì đến thông tin khác đến bảng master nên con không đề cập.

Vẫn còn 1 thông tin có mà bị bỏ qua (ngoài 3 thông tin tô đỏ): đó là ngày kiểm kê. Một mặt hàng, 1 kho, 1 lot có thể kiểm kê vài lần ở những ngày khác nhau.

 

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

Vẫn còn 1 thông tin có mà bị bỏ qua: đó là ngày kiểm kê. Một mặt hàng, 1 kho, 1 lot có thể kiểm kê vài lần ở những ngày khác nhau.

Dạ vâng đúng như vậy ạ, nếu là lấy từ cơ sở dữ liệu ban đầu thì là vậy nhưng là bởi vì khi truy vấn ngày tháng để lấy dữ liệu về con đã chọn tháng kiểm kê cùng từ ngày đến ngày nhập xuất rồi nên dữ liệu nằm trọn trong đó và coi như có thể bỏ qua mà chỉ xét phần tính toán thôi ạ,

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

Bạn chưa thấy dữ liệu gốc bao giờ.
Bảng 1: Dữ liệu tồn kho bao giờ cũng có: Nhiều kho, nhiều mặt hàng, mỗi kho nhiều mặt hàng, mỗi mặt hàng nhiều kho, mỗi mặt hàng nhiều lot nhập về.
Bảng 2: dữ liệu phát sinh (hàng ngày) cũng nhiều dạng như vậy và nhân lên nhiều ngày.
Không có trường khóa tạo quan hệ là chết chắc.
Mấu chốt CSDL bắt buộc phải vậy chứ phát sinh nhập xuất lot này, kho này mà lấy tồn đầu kho khác, lot khác là báo cáo vứt đi ngay.

Đúng vậy, tôi chỉ gợi ý. Đã biết SQL, hỏi trong chủ đề SQL, thì đáng lẽ chỉ gợi ý vậy thôi là làm được.

Đúng rồi chú, đây là dữ liệu nhiều mã kho nhiều mặt hàng và tổng hợp tất cả những gì nó có ạ.

 

[VetMini]

Đúng vậy chú ơi,các item xuất hiện bảng 2 chưa chắc đã có trong bảng 1 và cũng có thể có rất nhiều trong bảng 1.
Dữ liệu master ở bảng khác và đầy đủ các thông tin , mỗi item là duy nhất như chú nêu đó ạ. Các item phải được khai báo ở đây thì mới có dữ liệu ở bảng 1 và 2 được.
Nhưng vì chỉ đơn thuần tính toán nhập xuất tồn theo Item,Lot,Kho không tham chiếu gì đến thông tin khác đến bảng master nên con không đề cập.
...

Table1 của bạn ở trên là Master File.
Key chính của nó là Item+LotNo+StockNo (key kép).
Ở đâu dó, bạn có file TableX với key chính là Item, TableY với key chính là Item+LotNo thì chúng chỉ là hai bảng khác của Item, không ảnh hưởng gì đến hoạt động Mater~Transaction giữa Table1 và Table2 trong bài này.
Mỗi dòng trong Table2 là một transaction (phát sinh). Mỗi transaction mang một key để liên hệ nó với Table1. Ở đây cái key ấy là Item+LotNo+StockNo.
Lưu ý quan trọng: master file có thể mang điều kiện mỗi key là duy nhất, nhưng transaction file không hề bắt buộc. Một khóa Item+LotNo+StockNo có thể có nhiều transactions (liên hệ 1-N).
Đó là lý do khi tổng kết transactions, người ta thường có thêm đoạn Group by. Nếu trong bảng Table2, bạn bảo đảm được khóa Item+LotNo+StockNo là duy nất thì không cần phải Group.

SQL là ngôn ngữ được đặt ra để truy vấn CSDL LH. Cách thông dụng nhất để thực hiện LH (liên hệ) là dùng khóa (key).

Khi tôi nói Table2 của bạn không thiết kế đúng chuẩn thì từ "chuẩn (normalised)" ở đây nằm trong ngữ cảnh của CSDL LH. Tiếng Anh gọi là Level of Normalisation. Vì bảng này không thống nhất kiểu transaction - IN/OUT chỉ lả transaction đơn nhưng MOV là kép, bao gồm 1 IN và 1 OUT. Và CSDL như thế không đạt được cả chuẩn bậc 1. Thiết kế lý tưởng thì thường đạt đến bặc 3.

Những gì tôi giải thích cho bạn ở đây thuộc về tầm sâu của lý thuyết SQL (Structured Query Language) và CDSL LH (Relational DataBase). Nói chuyện tầm sâu thì phải chính xác. Tôi không biết tầm rộng cần gì và cũng không muốn nói chuyện tầm rộng.

 

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

Table1 của bạn ở trên là Master File.
Key chính của nó là Item+LotNo+StockNo (key kép).
Ở đâu dó, bạn có file TableX với key chính là Item, TableY với key chính là Item+LotNo thì chúng chỉ là hai bảng khác của Item, không ảnh hưởng gì đến hoạt động Mater~Transaction giữa Table1 và Table2 trong bài này.
Mỗi dòng trong Table2 là một transaction (phát sinh). Mỗi transaction mang một key để liên hệ nó với Table1. Ở đây cái key ấy là Item+LotNo+StockNo.
Lưu ý quan trọng: master file có thể mang điều kiện mỗi key là duy nhất, nhưng transaction file không hề bắt buộc. Một khóa Item+LotNo+StockNo có thể có nhiều transactions (liên hệ 1-N).
Đó là lý do khi tổng kết transactions, người ta thường có thêm đoạn Group by. Nếu trong bảng Table2, bạn bảo đảm được khóa Item+LotNo+StockNo là duy nất thì không cần phải Group.

SQL là ngôn ngữ được đặt ra để truy vấn CSDL LH. Cách thông dụng nhất để thực hiện LH (liên hệ) là dùng khóa (key).

Khi tôi nói Table2 của bạn không thiết kế đúng chuẩn thì từ "chuẩn (normalised)" ở đây nằm trong ngữ cảnh của CSDL LH. Tiếng Anh gọi là Level of Normalisation. Vì bảng này không thống nhất kiểu transaction - IN/OUT chỉ lả transaction đơn nhưng MOV là kép, bao gồm 1 IN và 1 OUT. Và CSDL như thế không đạt được cả chuẩn bậc 1. Thiết kế lý tưởng thì thường đạt đến bặc 3.

Những gì tôi giải thích cho bạn ở đây thuộc về tầm sâu của lý thuyết SQL (Structured Query Language) và CDSL LH (Relational DataBase). Nói chuyện tầm sâu thì phải chính xác. Tôi không biết tầm rộng cần gì và cũng không muốn nói chuyện tầm rộng.

Khổ cho con quá, đây là CSDL của bên phần mềm thiết kế bác ơi. Cái tên trường của họ đặt như thế nào con cũng bê nguyên như vậy bác ạ. Thôi thì họ muốn đặt tên là gì cũng được với con chỉ cần cái code để gọi là được bác.
Bác giúp con câu lệnh hoàn chỉnh với con vẫn đang loay hoay mà chưa ra ạ...
Có lẽ theo gợi ý của bác Hiếu con dùng VBA cho dễ ^^

 

[ptm0412]

Table1 của bạn ở trên là Master File.

Tôi còn gọi là master thứ cấp, vì phải dùng key là 3 field gộp lại, nếu master cấp 1 (danh mục) thì item phải là duy nhất và dùng làm trường khóa luôn.

IN/OUT chỉ lả transaction đơn nhưng MOV là kép, bao gồm 1 IN và 1 OUT.

Đó gọi là tiết kiệm không gian nhưng sẽ bị thiệt về thời gian khi cần tách ra. Không những vậy, nó còn không diễn tả được các trường hợp đặc biệt như sau:
1. Hao hụt mất mát khi chuyển kho: gói kẹo mang từ phòng này qua phòng kia còn bị mất mát nữa là hàng chuyển từ kho nọ qua kho kia phải qua khâu vận chuyển
2. Hàng chuyển kho đã xuất ra khỏi kho đi, nhưng còn đang trên đường, chưa đến kho nhận. Kho nhận chưa thể ghi nhận tăng số lượng. Nếu nhằm ngày cuối tháng, tệ hơn là nhằm ngày cuối năm tài chính thì kiểm kê thiếu hụt cả tỷ tiền hàng.

Cách giải quyết:
- tách 2 chứng từ giao dịch: xuất chuyển kho và nhập chuyển kho
- Mỗi chứng từ có số lượng theo yêu cầu và số thực tế
- Có kho trung gian gọi là kho đi đường (kho ảo). KIểm kê kho ảo mà còn tồn tức là: một: hàng chưa đến, hai: nếu hàng đã đến mà thực nhận ít hơn, hàng mất mát tổn thất trên đường đi.

 

[VetMini]

...
Có lẽ theo gợi ý của bác Hiếu con dùng VBA cho dễ ^^

Anh ấy nói đúng. Trường hợp này dùng con bài cũ Đít sần tốt hơn nhiều.

ADO sử dụng SQL, mà SQL thì CSDL phải đạt ít nhất là chuẩn bậc 2 mới dễ hoạt động. Table2 của bạn chưa đạt được bậc 1 (cán dẹp/flatten - tức không có chuyện phát sinh kép) cho nên chính câu SQL phải làm chuyện "cán dẹp" này.
Tôi không muốn viết câu SQL cho bài này vì nó khá phức tạp mà tôi không nghĩ là bạn học được bao nhiêu (vì nó quá đặc thù).

 

[xì tin]

Cảm ơn bạn đã giúp đỡ,
OT thử trường hợp này kết quả thấy khác với kết quả mong muốn.
Nhờ bạn kiểm tra giúp ạ.

Như tôi đã nói là bạn phải miêu tả từng trường hợp cụ thể. Tôi không rành về VBA nên không có hiểu code bạn viết nó như thề nào.

 

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

Bạn phải miêu tả là làm sao ra được số lượng StockIn và StockOut nha hôn.

Như tôi đã nói là bạn phải miêu tả từng trường hợp cụ thể. Tôi không rành về VBA nên không có hiểu code bạn viết nó như thề nào.

Thật khâm phục bạn, khi chưa hiểu bản chất mà vẫn có thể viết được câu lệnh truy vấn.
Vậy OT lấy file bài 225:

Truy vấn trong SQL?

Nếu tài khoản của bạn của bạn chỉ được quyền Read thì việc tạo Table sẽ không được, nhưng Table tạm vẫn được, khi bạn không sử dụng SQL nữa Table đó sẽ tự xóa, tôi chỉnh lại code trên bằng việc sử dụng Table tạm SELECT TDK.[MA_HANG],TDK.[KHO_LUU_TRU],TDK.[LOTNO], TDK.[SO_LUONG] [TONDAUKY], 0...

www.giaiphapexcel.com

để giải thích = công thức ạ:

T3 = SUMIFS($D$3:$D$6,$A$3:$A$6,$Q3,$B$3:$B$6,$S3,$C$3:$C$6,$R3)

U3 = SUMIFS($N$3:$N$29,$H$3:$H$29,$Q3,$J$3:$J$29,$S3,$M$3:$M$29,$R3,$G$3:$G$29,"IN")
+SUMIFS($N$3:$N$29,$H$3:$H$29,$Q3,$I$3:$I$29,$S3,$M$3:$M$29,$R3,$G$3:$G$29,"MOV")

V3 = SUMIFS($N$3:$N$29,$H$3:$H$29,$Q3,$J$3:$J$29,$S3,$M$3:$M$29,$R3,$G$3:$G$29,"OUT")
+SUMIFS($N$3:$N$29,$H$3:$H$29,$Q3,$J$3:$J$29,$S3,$M$3:$M$29,$R3,$G$3:$G$29,"MOV")

W3 = T3+U3-V3

 

[ptm0412]

Nếu bảng 1 có trường khóa như sau:



Bảng 2 có trường khóa như sau:



Thì câu SQL sẽ là:

SELECT NXT.*, iif([Opening] <> 0 ,[Opening],0)+[QtyIn]-[QtyOut] AS Ending
FROM
(SELECT Data.Item, Data.LotNo, "Kho_004" as WH, Ton.Quantity AS Opening,
Sum(IIf([StockType]="IN" Or ([StockType]="Mov" And [Data].[StockNoTo]="KHO_004"),[DAta].[Quantity],0)) AS QtyIn,
Sum(IIf([StockType]="OUT"   Or ([StockType]="Mov" And [Data].[StockNo]="KHO_004"),[DAta].[Quantity],0)) AS QtyOut
FROM Data LEFT JOIN Ton ON Data.ID = Ton.ID
GROUP BY Data.Item, Data.LotNo, Ton.Quantity) NXT;

Kết quả (làm trong Access):

 

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

Nếu bảng 1 có trường khóa như sau:

View attachment 291918

Bảng 2 có trường khóa như sau:

View attachment 291919

Thì câu SQL sẽ là:

SELECT NXT.*, iif([Opening] <> 0 ,[Opening],0)+[QtyIn]-[QtyOut] AS Ending
FROM
(SELECT Data.Item, Data.LotNo, "Kho_004" as WH, Ton.Quantity AS Opening,
Sum(IIf([StockType]="IN" Or ([StockType]="Mov" And [Data].[StockNoTo]="KHO_004"),[DAta].[Quantity],0)) AS QtyIn,
Sum(IIf([StockType]="OUT"   Or ([StockType]="Mov" And [Data].[StockNo]="KHO_004"),[DAta].[Quantity],0)) AS QtyOut
FROM Data LEFT JOIN Ton ON Data.ID = Ton.ID
GROUP BY Data.Item, Data.LotNo, Ton.Quantity) NXT;

Kết quả (làm trong Access):

View attachment 291921

Câu lệnh này chạy trong môi trường SQL hay ADO thì sửa lại thế nào vậy chú Mỹ .
Con không có Access ạ.

 

[VetMini]

Câu lệnh này chạy trong môi trường SQL hay ADO thì sửa lại thế nào vậy chú Mỹ .
Con không có Access ạ.

SQL là một ngôn ngữ được tiêu chuẩn hóa để truy vấn CSDL LH. Chả có môi trường nào gọi là SQL cả. Mõi môi trường có một phiên bản riêng của chúng. Ví dụ Access thì dùng Access Query, SQL Server thì dùng TSQL, Oracle thì dùng PL-SQL (hay SQL-Plus)
ADO (Active Data Object) là một Object của MS. Trong VBA, ADO dùng để kết nối với CSDL và truy vấn. Nếu kết nối với Excel thì phải dùng cổ máy Access để truy vấn cho nên câu truy vấn phải viết theo Access. Nếu kết nối vói SQL Server thì câu truy vấn viết theo TSQL.

Chỉnh:
1. Đặt mỗi dòng vào giữa cặp "".
2. Đầu mỗi dòng, chèn thêm một dấu cách giữa " và ký tự đầu tiên.
3. Cuối mỗi dòng, trừ dòng cuối, chèn thêm & _ (dấu ampersand, một dấu cách, và dấu gạch dưới)
4. Trong dòng, bất cứ chỗ nào có " thì đổi nó thành ""
Như vậy câu thứ 3 sẽ là
" SELECT Data.Item, Data.LotNo, ""Kho_004"" as WH, Ton.Quantity AS Opening," & _
Và dòng cuối là
" GROUP BY Data.Item, Data.LotNo, Ton.Quantity) NXT;"

1. Lập chuỗi để cho vào chuỗi sqlStr
2. Dấu cách là mẹo viết SQL string, dùng để bảo đảm nối chuỗi thì không bị dính chữ.
3. Ngữ pháp VBA: & là toán tử nối chuỗi, _ là bảo trình dịch rằng "dòng này chưa xong, cần nối với dòng tiếp theo"
4. Ngữ pháp VBA: Bên trong cặp "" thì bất cứ " nào cũng phải đổi thành "", nếu có "" thì đổi thành """"

 

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

SQL là một ngôn ngữ được tiêu chuẩn hóa để truy vấn CSDL LH. Chả có môi trường nào gọi là SQL cả. Mõi môi trường có một phiên bản riêng của chúng. Ví dụ Access thì dùng Access Query, SQL Server thì dùng TSQL, Oracle thì dùng PL-SQL (hay SQL-Plus)
ADO (Active Data Object) là một Object của MS. Trong VBA, ADO dùng để kết nối với CSDL và truy vấn. Nếu kết nối với Excel thì phải dùng cổ máy Access để truy vấn cho nên câu truy vấn phải viết theo Access. Nếu kết nối vói SQL Server thì câu truy vấn viết theo TSQL.

Chỉnh:
1. Đặt mỗi dòng vào giữa cặp "".
2. Đầu mỗi dòng, chèn thêm một dấu cách giữa " và ký tự đầu tiên.
3. Cuối mỗi dòng, trừ dòng cuối, chèn thêm & _ (dấu ampersand, một dấu cách, và dấu gạch dưới)
4. Trong dòng, bất cứ chỗ nào có " thì đổi nó thành ""
Như vậy câu thứ 3 sẽ là
" SELECT Data.Item, Data.LotNo, ""Kho_004"" as WH, Ton.Quantity AS Opening," & _
Và dòng cuối là
" GROUP BY Data.Item, Data.LotNo, Ton.Quantity) NXT;"

1. Lập chuỗi để cho vào chuỗi sqlStr
2. Dấu cách là mẹo viết SQL string, dùng để bảo đảm nối chuỗi thì không bị dính chữ.
3. Ngữ pháp VBA: & là toán tử nối chuỗi, _ là bảo trình dịch rằng "dòng này chưa xong, cần nối với dòng tiếp theo"
4. Ngữ pháp VBA: Bên trong cặp "" thì bất cứ " nào cũng phải đổi thành "", nếu có "" thì đổi thành """"

Con sửa như thế mà không chạy được , bác xem giúp con với:

    query = "SELECT NXT.*, iif([Opening] <> 0 ,[Opening],0)+[QtyIn]-[QtyOut] AS Ending " & _
            "FROM (SELECT Data.Item, Data.LotNo, 'Kho_004' as WH, Ton.Quantity AS Opening, " & _
                "Sum(IIf([StockType]='IN' Or ([StockType]='Mov' And Data.[StockNoTo]='KHO_004'),Data.[Quantity],0)) AS QtyIn, " & _
                "Sum(IIf([StockType]='OUT' Or ([StockType]='Mov' And Data.[StockNo]='KHO_004'),Data.[Quantity],0)) AS QtyOut " & _
            "FROM [NXT$G2:O29] Data LEFT JOIN [NXT$A2:E6] Ton ON Data.ID = Ton.ID " & _
            "GROUP BY Data.Item, Data.LotNo, Ton.Quantity) NXT;"

 

[ptm0412]

Con sửa như thế mà không chạy được , bác xem giúp con với:

Data và Ton là tên bảng. Trong excel nó phải là cái gì đó với tên tương ứng. Thế đã sửa tên chưa?

 

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

Data và Ton là tên bảng. Trong excel nó phải là cái gì đó với tên tương ứng. Thế đã sửa tên chưa?

Con sửa rồi đây mà chú:
"FROM [NXT$G2:O29] Data LEFT JOIN [NXT$A2:E6] Ton ON Data.ID = Ton.ID "

 

[VetMini]

Làm việc từng giai đoạn một.
Đã thử
" Sectect *" & _
" FROM [NXT$G2:O29] Data LEFT JOIN [NXT$A2:E6] Ton ON Data.ID = Ton.ID "
Xem nó ra cái gì?

Chú ý:
Để bảo đảm không bị dính chữ khi nối hai câu, tôi đặt dấu cách ở đầu câu. Đặt ở cuối câu cũng được (có người nói tốt hơn) nhưng tôi có tật lười scroll qua bên phải để xem và kiểm định nó.

 

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

Làm việc từng giai đoạn một.
Đã thử
" Sectect *" & _
" FROM [NXT$G2:O29] Data LEFT JOIN [NXT$A2:E6] Ton ON Data.ID = Ton.ID "
Xem nó ra cái gì?

Chú ý:
Để bảo đảm không bị dính chữ khi nối hai câu, tôi đặt dấu cách ở đầu câu. Đặt ở cuối câu cũng được (có người nói tốt hơn) nhưng tôi có tật lười scroll qua bên phải để xem và kiểm định nó.

Con thử thấy lỗi "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."
Và con cũng thử câu lệnh này:

query = "SELECT Data.Item, Data.LotNo, 'Kho_004' AS WH, Ton.Quantity AS Opening, " & _
        "Sum(IIf([StockType]='IN' Or ([StockType]='Mov' And Data.[StockNoTo]='KHO_004'), Data.[Quantity], 0)) AS QtyIn, " & _
        "Sum(IIf([StockType]='OUT' Or ([StockType]='Mov' And Data.[StockNo]='KHO_004'), Data.[Quantity], 0)) AS QtyOut " & _
        "FROM [NXT$G2:O29] AS Data " & _
        "LEFT JOIN [NXT$A2:E6] AS Ton ON Data.ID = Ton.ID " & _
        "GROUP BY Data.Item, Data.LotNo, Ton.Quantity;"

cũng lỗi:
No value given for one or more required parameters.

 

[VetMini]

Con thử thấy lỗi "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."
...

Xin lỗi, tôi gõ nhầm, mắt già kèm nhèm.
SELECT chứ không phải là Sectect

Cái lỗi nó báo có nghĩa là nó muốn đầu một câu truy vấn phải là một trong những từ khóa mà nó liệt ra: 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', 'UPDATE'.

 

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

Xin lỗi, tôi gõ nhầm, mắt già kèm nhèm.
SELECT chứ không phải là Sectect

Cái lỗi nó báo có nghĩa là nó muốn đầu một câu truy vấn phải là một trong những từ khóa mà nó liệt ra: 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', 'UPDATE'.

Vâng như vậy cũng giống lỗi câu lệnh trên rồi bác:
No value given for one or more required parameters.

 

[VetMini]

Thớt này xcos đã mấy năm. Ngỡ giờ này bạn đã khá thành thạo cách debug SQL rồi.

Cách căn bản nhất là tách nhỏ dần.
Nếu câu trên vẫn lỗi thì thử:
" Select Data.*" & _
" FROM [NXT$G2:O29] Data"

 

[ptm0412]

Tốt nhất là không dùng SQL cho trường hợp này. Dùng 3 trường gộp lại thành 1 trường khóa cũng chỉ là chữa cháy công nghệ cao thôi, chứ không lấy được trường hợp có tồn nhưng không nhập xuất, hoặc trường hợp không tòn nhưng có nhập xuất. Lý do SQL chỉ có Left Join (lấy hết bên trái) hoặc Right Join (lấy hết bên phải), không có loại Full join (lấy hết 2 bên).

Cách thay thế thì tôi ưu tiên dùng Power query, nó có full join. Power query trong file đính kèm có thể lọc dữ liệu bao gồm nhiều mặt hàng, nhiều kho, chỉ cần chọn trong 2 ô J1 và J2 rồi refresh

 

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

Thớt này xcos đã mấy năm. Ngỡ giờ này bạn đã khá thành thạo cách debug SQL rồi.

Cách căn bản nhất là tách nhỏ dần.
Nếu câu trên vẫn lỗi thì thử:
" Select Data.*" & _
" FROM [NXT$G2:O29] Data"

Tốt nhất là không dùng SQL cho trường hợp này. Dùng 3 trường gộp lại thành 1 trường khóa cũng chỉ là chữa cháy công nghệ cao thôi, chứ không lấy được trường hợp có tồn nhưng không nhập xuất, hoặc trường hợp không tòn nhưng có nhập xuất. Lý do SQL chỉ có Left Join (lấy hết bên trái) hoặc Right Join (lấy hết bên phải), không có loại Full join (lấy hết 2 bên).

Cách thay thế thì tôi ưu tiên dùng Power query, nó có full join. Power query trong file đính kèm có thể lọc dữ liệu bao gồm nhiều mặt hàng, nhiều kho, chỉ cần chọn trong 2 ô J1 và J2 rồi refresh

View attachment 291946

View attachment 291947

View attachment 291948

Cảm ơn bác VetMini và chú Mỹ đã luôn giúp con.. vấn đề này cũng khá rắc rối với SQL. Còn với Power query thì con đã biết nó rất lợi hại nhưng do môi trường nhiều người dùng nên có lẽ con dùng vba để phù hợp với nhu cầu công việc của mình ạ.

 

[xì tin]

Cảm ơn bạn đã giúp đỡ,
OT thử trường hợp này kết quả thấy khác với kết quả mong muốn.
Nhờ bạn kiểm tra giúp ạ.

Ủa câu lệnh mà mình viết nó ra kết quả đúng với kết quả bạn mong muốn mà?

Sub TongHop()
    Dim strSQL As String
    strSQL = "Select Item,LotNo, StockNo,0 as OpeningStock,iif(StockType='IN',Quantity,0) as StockIn,iif(StockType='IN',0,Quantity) as StockOut From [NXT$G2:O] Union All Select Item,LotNo,StockNoTo,0,Quantity,0 From [NXT$G2:O] Where StockType Like 'MOV' Union All Select Item,LotNo,StockNo,Quantity,0,0 From [NXT$A2:E]"
    With CreateObject("ADODB.Connection")
        .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 12.0 Xml;"
        Sheet3.Range("Y15").CopyFromRecordset .Execute("Select Item,LotNo,StockNo,Sum(OpeningStock) as OpeningStock,Sum(StockIn) as StockIn,Sum(StockOut) as StockOut,(Sum(OpeningStock)+Sum(StockIn)-Sum(StockOut)) as StockRemain From (" & strSQL & ") Where StockNo Like 'KHO_004' Group By Item,LotNo,StockNo")
    End With
End Sub

 

[VetMini]

Cảm ơn bác VetMini và chú Mỹ đã luôn giúp con.. vấn đề này cũng khá rắc rối với SQL. Còn với Power query thì con đã biết nó rất lợi hại nhưng do môi trường nhiều người dùng nên có lẽ con dùng vba để phù hợp với nhu cầu công việc của mình ạ.

1. tôi không hiểu nổi. Môi trường nhiều người dùng mới là nơi tránh VBA. Bạn đưa người ta một file xlsm là người ta đã ngại. Chỉnh sửa xong người ta trả lại cũng file ấy, bạn cũng ngại. Bất cứ lúc nào cũng có thể nhiễm vi-rít.

2. muốn "dùng VBA để phù hợp" cũng chả sao. Nhưng có ai bắt buộc phải dùng ADODB? Cách thiết kế CSDL của bạn đã được phê rõ rệt là không phù hợp với truy vấn bằng SQL. Bạn đâu đến nổi tệ về Dictionary. Code theo chiều hướng đó dễ kiểm soát hơn.

 

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

Ủa câu lệnh mà mình viết nó ra kết quả đúng với kết quả bạn mong muốn mà?

Sub TongHop()
    Dim strSQL As String
    strSQL = "Select Item,LotNo, StockNo,0 as OpeningStock,iif(StockType='IN',Quantity,0) as StockIn,iif(StockType='IN',0,Quantity) as StockOut From [NXT$G2:O] Union All Select Item,LotNo,StockNoTo,0,Quantity,0 From [NXT$G2:O] Where StockType Like 'MOV' Union All Select Item,LotNo,StockNo,Quantity,0,0 From [NXT$A2:E]"
    With CreateObject("ADODB.Connection")
        .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 12.0 Xml;"
        Sheet3.Range("Y15").CopyFromRecordset .Execute("Select Item,LotNo,StockNo,Sum(OpeningStock) as OpeningStock,Sum(StockIn) as StockIn,Sum(StockOut) as StockOut,(Sum(OpeningStock)+Sum(StockIn)-Sum(StockOut)) as StockRemain From (" & strSQL & ") Where StockNo Like 'KHO_004' Group By Item,LotNo,StockNo")
    End With
End Sub

View attachment 291955

Cảm ơn bạn rất nhiều.

1. tôi không hiểu nổi. Môi trường nhiều người dùng mới là nơi tránh VBA. Bạn đưa người ta một file xlsm là người ta đã ngại. Chỉnh sửa xong người ta trả lại cũng file ấy, bạn cũng ngại. Bất cứ lúc nào cũng có thể nhiễm vi-rít.

2. muốn "dùng VBA để phù hợp" cũng chả sao. Nhưng có ai bắt buộc phải dùng ADODB? Cách thiết kế CSDL của bạn đã được phê rõ rệt là không phù hợp với truy vấn bằng SQL. Bạn đâu đến nổi tệ về Dictionary. Code theo chiều hướng đó dễ kiểm soát hơn.

À vì mọi người không phải ai cũng biết code hay Power query nên cứ muốn một nhát ra luôn đó bác.

 

[ptm0412]

Kể cả Power query vẫn bị rắc rối vì chỉ có thể xem từng kho một. Khi xem 2 kho 1 lúc trở lên thì các dòng kiểu MOV chỉ xuất hiện tại 1 trong 2 kho, vì không thể nhân bản. SQL lại càng không thể.
VBA Dict thì có thể nhưng phải 2 Dict, mục đích là vét hết dữ liệu của cả 2 bảng: tồn không nhập xuất và không tồn đầu nhưng có nhập xuất.
Cảm ơn bạn rất nhiều.

À vì mọi người không phải ai cũng biết code hay Power query nên cứ muốn một nhát ra luôn đó bác.

Cả 3 thứ đều 1 nhát ra luôn mà? ngụy biện vừa thôi.

 

[ptm0412]

Power query có thể xem cùng lúc chỉ 1 kho (ở J1), chọn mặt hàng ở J2 thì xem 1 mặt hàng, bỏ trống thì xem mọi mặt hàng

 

[ptm0412]

Này thì VBA, này thì Dict (chỉ 1 Dict)
Có thể xem 1 mặt hàng hoặc xem tất cả (điền M1 hoặc bỏ trống)






Có thể xem 1 kho hay nhiều kho (điền K1, K2, K3, ...)
Khi xem nhiều kho, loại MOV xuất hiện 2 dòng như hình:

 

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

Này thì VBA, này thì Dict (chỉ 1 Dict)
Có thể xem 1 mặt hàng hoặc xem tất cả (điền M1 hoặc bỏ trống)

View attachment 292015

View attachment 292016


Có thể xem 1 kho hay nhiều kho (điền K1, K2, K3, ...)
Khi xem nhiều kho, loại MOV xuất hiện 2 dòng như hình:

View attachment 292017

Quả thực chú Mỹ đã bắt đầu ngẫu hứng ahihi

 

[ptm0412]

Quả thực chú Mỹ đã bắt đầu ngẫu hứng ahihi

Chẳng qua là thấy ghét câu "một nhát ra luôn" mà cứ đòi SQL.
Với lại VBA kỳ này đố nhóc đọc hiểu, dù chỉ là dùng Dic và mảng

 

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

Chẳng qua là thấy ghét câu "một nhát ra luôn" mà cứ đòi SQL.
Với lại VBA kỳ này đố nhóc đọc hiểu, dù chỉ là dùng Dic và mảng

Giờ con mới xem code đúng là rất công phu chú ạ.
Tạm thời con nhờ anh 'rô bốt' dịch giúp đã ạ rồi sau khi có thời gian con nghiên cứu lại:

Sub NXT()
    Dim DictTon As Object, DictNX As Object
    Dim IDKey As String, WH As String, Item As String
    Dim NextRw As Long, LastRw As Long, Rws As Long
    Dim ArrTon() As Variant, ArrNX() As Variant, ArrKq() As Variant, ArrKho() As Variant
    Dim ArrDItems As Variant, DItemi As Variant
    Dim j As Long, i As Long, k As Long, n As Long
    
    ' Tạo đối tượng từ lớp Dictionary để lưu trữ thông tin về tồn kho
    Set DictTon = CreateObject("Scripting.Dictionary")
    ' Tạo đối tượng từ lớp Dictionary để lưu trữ thông tin về nhận xuất
    Set DictNX = CreateObject("Scripting.Dictionary")
    
    Item = Sheet3.[M1] ' Lấy giá trị ô M1 trên Sheet3
    
    ' Xóa bộ lọc tự động trên Sheet "Ton"
    Sheets("Ton").AutoFilterMode = False
    LastRw = Sheets("Ton").[A10000].End(xlUp).Row ' Tìm dòng cuối cùng trong cột A trên Sheet "Ton"
    ArrTon = Sheets("Ton").Range("A2:D" & LastRw).Value ' Gán giá trị của vùng dữ liệu từ A2 đến D(LastRw) vào mảng ArrTon
    Rws = Rws + LastRw
    
    ' Xóa bộ lọc tự động trên Sheet "Data"
    Sheets("Data").AutoFilterMode = False
    LastRw = Sheets("Data").[A10000].End(xlUp).Row ' Tìm dòng cuối cùng trong cột A trên Sheet "Data"
    ArrNX = Sheets("Data").Range("A2:H" & LastRw).Value ' Gán giá trị của vùng dữ liệu từ A2 đến H(LastRw) vào mảng ArrNX
    Rws = Rws + LastRw
    
    LastRw = Sheet3.[K100].End(xlUp).Row ' Tìm dòng cuối cùng trong cột K trên Sheet3
    If LastRw > 1 Then
        ArrKho = Sheet3.Range("K1:K" & LastRw).Value ' Gán giá trị của vùng dữ liệu từ K1 đến K(LastRw) vào mảng ArrKho
    Else
        ReDim ArrKho(1 To 1, 1 To 1)
        ArrKho(1, 1) = Sheet3.[K1].Value ' Gán giá trị ô K1 trên Sheet3 vào mảng ArrKho
    End If
    
    Sheet3.[B4].Resize(10000, 7).Clear ' Xóa dữ liệu trong vùng từ ô B4 đến G10003 trên Sheet3
    
    For j = 1 To UBound(ArrKho, 1)
        WH = ArrKho(j, 1) ' Lấy giá trị từng phần tử trong mảng ArrKho
        
        ReDim ArrKq(1 To Rws, 1 To 7)
        
        ' Duyệt qua từng dòng trong mảng ArrTon
        For i = 1 To UBound(ArrTon, 1)
            ' Kiểm tra điều kiện để thêm dữ liệu vào Dictionary DictTon
            If Item = "" And ArrTon(i, 2) = WH Then
                k = k + 1
                ' Thêm dữ liệu vào Dictionary DictTon với key là sự kết hợp của cột A, cột B, cột C
                ' Value chứa số thứ tự, giá trị cột D và số thứ tự hàng
                DictTon.Add ArrTon(i, 1) & ArrTon(i, 2) & ArrTon(i, 3), k & "|" & ArrTon(i, 4) & "|" & i
            Else
                If ArrTon(i, 1) = Item And ArrTon(i, 2) = WH Then
                    k = k + 1
                    DictTon.Add ArrTon(i, 1) & ArrTon(i, 2) & ArrTon(i, 3), k & "|" & ArrTon(i, 4) & "|" & i
                End If
            End If
        Next
        
        K1 = DictTon.Count ' Số lượng phần tử trong Dictionary DictTon
        
        ' Duyệt qua từng dòng trong mảng ArrNX
        For i = 1 To UBound(ArrNX, 1)
            ' Kiểm tra điều kiện để tạo key và thêm dữ liệu vào Dictionary DictTon
            If (Item <> "" And ArrNX(i, 2) = Item And (ArrNX(i, 3) = WH Or ArrNX(i, 4) = WH)) Or Item = "" _
            And (ArrNX(i, 3) = WH Or ArrNX(i, 4) = WH) Then
                IDKey = ArrNX(i, 2) & WH & ArrNX(i, 7) ' Tạo key từ sự kết hợp của cột B, cột C và cột H
                ' Kiểm tra nếu key chưa tồn tại trong Dictionary DictTon
                If Not DictTon.exists(IDKey) Then
                    k = k + 1
                    DictTon.Add IDKey, k & "|" & 0
                End If
                n = Val(Split(DictTon.Item(IDKey), "|")(0)) ' Lấy số thứ tự từ value của key trong Dictionary DictTon
                ArrKq(n, 1) = ArrNX(i, 2)
                ArrKq(n, 2) = ArrNX(i, 7)
                ArrKq(n, 3) = WH
                ArrKq(n, 4) = Split(DictTon.Item(IDKey), "|")(1)
                ArrKq(n, 5) = ArrKq(n, 5) + IIf(ArrNX(i, 1) = "IN" Or ArrNX(i, 3) = WH, ArrNX(i, 8), 0)
                ArrKq(n, 6) = ArrKq(n, 6) + IIf(ArrNX(i, 1) = "OUT" Or (ArrNX(i, 1) = "MOV" And ArrNX(i, 4) = WH), ArrNX(i, 8), 0)
                ArrKq(n, 7) = ArrKq(n, 4) + ArrKq(n, 5) - ArrKq(n, 6)
                Debug.Print i, n, ArrKq(2, 6) ' In ra thông tin debug
            End If
        Next
        
        ArrDItems = DictTon.Items ' Gán các giá trị từ Dictionary DictTon vào mảng ArrDItems
        
        ' Duyệt qua từng dòng trong mảng ArrKq
        For i = 1 To k
            If ArrKq(i, 1) = "" Then
                DItemi = Split(ArrDItems(i - 1), "|") ' Tách các giá trị từ phần tử thứ (i-1) trong mảng ArrDItems
                ArrKq(i, 1) = ArrTon(DItemi(2), 1)
                ArrKq(i, 2) = ArrTon(DItemi(2), 3)
                ArrKq(i, 3) = WH
                ArrKq(i, 4) = DItemi(1)
                ArrKq(i, 5) = 0
                ArrKq(i, 6) = 0
                ArrKq(i, 7) = DItemi(1)
            End If
        Next
        
        NextRw = Sheet3.[B10000].End(xlUp).Row + 1 ' Tìm dòng cuối cùng trong cột B trên Sheet3 và tăng giá trị lên 1
        If k > 0 Then Sheet3.Cells(NextRw, 2).Resize(k, 7) = ArrKq ' Gán giá trị từ mảng ArrKq vào vùng dữ liệu trên Sheet3
        DictTon.RemoveAll ' Xóa tất cả phần tử trong Dictionary DictTon
        k = 0 ' Reset giá trị của biến k về 0
    Next
    
    Set DictTon = Nothing ' Gán giá trị Nothing cho đối tượng DictTon để giải phóng bộ nhớ
End Sub

 

[befaint]

.AutoFilterMode = False

Nguyên tắc là không được xóa, làm mất cái xây dựng trên bảng tính. Chỉ được bỏ chọn lọc đi mà thôi.

[B4].Resize(10000, 7).Clear

Sẽ làm mất hết định dạng đã có và sẽ 'dính vết' tới vùng hết vùng đó => Nặng file.

 

[ptm0412]

Giờ con mới xem code đúng là rất công phu chú ạ.
Tạm thời con nhờ anh 'rô bốt' dịch giúp đã ạ rồi sau khi có thời gian con nghiên cứu lại:

Hiểu được câu lệnh mới là chuyện nhỏ. Chuyện lớn là các câu hỏi tại sao:
Tại sao tạo Dict từ bảng tồn, rồi tại sao lại bổ sung Dict từ bảng NX?
Tại sao điều kiện and or lắm thế?
Tại sao item của key trong Dict phải lấy 3 thành phần? Khi nào xài thành phần nào?
Tại sao thêm vòng lặp cuối?
Tại sao bỏ trống item lại có thể lấy hết?
Tại sao warehouse không bỏ trống để lấy hết?
Càng hỏi nhiều tại sao càng giỏi, chứ nhắm mắt chép về hiểu lơ mơ sau này làm sao tự viết.

 

[HieuCD]

Cảm ơn chú Mỹ và bạn @xì tin đã quan tâm và góp ý.
Chú Mỹ giúp con câu lệnh hoàn chỉnh với, con loay hoay mãi rồi nhưng chưa được.
Nếu là VBA thì code sẽ như thế này ạ:

Option Explicit

Sub testCalcInventory()

    Dim dic As Object, sheet As Worksheet
    Dim inventory As Variant, scan As Variant, result As Variant, Key As Variant
    Dim sPart As String, sLot As String, sTockNo As String, sTockNoTo As String, str As String, sTockNoSearch As String
    Dim i As Long, j As Long, r As Long, u As Long, k As Long
    Dim quantity As Double
    Dim c As Integer, style As String
 
    Const sDELIM As String = "|"
 
    Set sheet = ThisWorkbook.ActiveSheet
    inventory = sheet.Range("A3:E6").Value
    scan = sheet.Range("G3:O29").Value
 
    sTockNoSearch = "*KHO_004*"
 
    If IsArray(inventory) Then r = UBound(inventory, 1)
    If IsArray(scan) Then r = r + UBound(scan, 1)
 
    If (r = 0) Then
        Exit Sub
    End If
 
    If Not IsArray(scan) Then
        GoTo Iventory_
    End If
 
Scaner_:
 
    Set dic = CreateObject("Scripting.Dictionary")
    c = 8:     ReDim result(1 To r * 2, 1 To c)
 
    For i = LBound(scan, 1) To UBound(scan, 1)
    
        style = scan(i, 1)
        sPart = scan(i, 2)
        sTockNoTo = scan(i, 3)
        sTockNo = scan(i, 4)
        sLot = scan(i, 7)
        quantity = scan(i, 8)

        str = Join(Array(sPart, sTockNoTo, sLot), sDELIM)
        If Not dic.Exists(str) And (style = "MOV") Then
            If UCase(sTockNoTo) Like UCase(sTockNoSearch) Then
                k = k + 1
                dic.Add str, k
                result(k, 1) = k
                result(k, 2) = sPart
                result(k, 3) = sLot
                result(k, 4) = sTockNoTo
            End If
        End If
    
        Key = Join(Array(sPart, sTockNo, sLot), sDELIM)
        If Not dic.Exists(Key) Then
            If UCase(sTockNo) Like UCase(sTockNoSearch) Then
                k = k + 1
                dic.Add Key, k
                result(k, 1) = k
                result(k, 2) = sPart
                result(k, 3) = sLot
                result(k, 4) = sTockNo
            End If
        End If
    
        u = dic.Item(str):  r = dic.Item(Key)
    
        Select Case style
           Case "IN" 'NHAP KHO
                If (r > 0) Then result(r, 6) = result(r, 6) + quantity
                If (r > 0) Then result(r, 8) = result(r, 8) + quantity
           Case "OUT" 'XUAT KHO
                If (r > 0) Then result(r, 7) = result(r, 7) + quantity
                If (r > 0) Then result(r, 8) = result(r, 8) - quantity
           Case "MOV" 'CHUYEN KHO
                If (u > 0) Then result(u, 6) = result(u, 6) + quantity
                If (u > 0) Then result(u, 8) = result(u, 8) + quantity
                If (r > 0) Then result(r, 7) = result(r, 7) + quantity
                If (r > 0) Then result(r, 8) = result(r, 8) - quantity
        End Select
            
    Next i

    If Not IsArray(inventory) Then
        GoTo Result_
    End If
 
Iventory_:
    For i = LBound(inventory, 1) To UBound(inventory, 1)
        sPart = inventory(i, 1)
        sTockNo = inventory(i, 2)
        sLot = inventory(i, 3)
        quantity = inventory(i, 4)
        Key = Join(Array(sPart, sTockNo, sLot), sDELIM)
        str = Join(Array(sDELIM, sPart, sTockNo, sLot), sDELIM)
        If UCase(sTockNo) Like UCase(sTockNoSearch) Then
            If Not dic.Exists(Key) And (quantity > 0) Then
                k = k + 1
                dic.Add Key, k
                result(k, 1) = k
                result(k, 2) = sPart
                result(k, 3) = sLot
                result(k, 4) = sTockNo
                result(k, 5) = quantity
                result(k, 6) = quantity
            Else
                If Not dic.Exists(str) And (quantity > 0) Then
                    dic.Add str, quantity:  r = dic.Item(Key)
                    result(r, 5) = quantity
                    result(r, 8) = result(r, 5) + result(r, 6) - result(r, 7)
                End If
            End If
        End If
    Next i
 
Result_:
    If (k > 0) Then sheet.Range("P13").Resize(k, c).Value = result
 
End Sub

Rút gọn code lại

Sub XYZ()
  Dim dic As Object, sh As Worksheet, aInve(), aScan(), res()
  Dim srI&, srD&, i&, k&, Q#, dau&
  Const kho$ = "*KHO_004*"
 
  Set sh = ThisWorkbook.ActiveSheet
  aInve = sh.Range("A3:E6").Value
  aScan = sh.Range("G3:O29").Value
  srI = UBound(aInve, 1): srD = UBound(aScan, 1)
  ReDim res(1 To srI + srD * 2, 1 To 8)
  Set dic = CreateObject("Scripting.Dictionary")
  For i = 1 To srI
    If aInve(i, 2) Like kho Then
      Call AddRes(dic, res, k, Array(aInve(i, 1), aInve(i, 3), aInve(i, 2)), aInve(i, 4), 5, 1)
    End If
  Next i
  For i = 1 To srD
    If aScan(i, 4) Like kho Then
      If aScan(i, 1) = "OUT" Then
        Call AddRes(dic, res, k, Array(aScan(i, 2), aScan(i, 7), aScan(i, 4)), aScan(i, 8), 7, -1)
      ElseIf aScan(i, 1) = "IN" Then
        Call AddRes(dic, res, k, Array(aScan(i, 2), aScan(i, 7), aScan(i, 4)), aScan(i, 8), 6, 1)
      Else
        Call AddRes(dic, res, k, Array(aScan(i, 2), aScan(i, 7), aScan(i, 4)), aScan(i, 8), 7, -1)
      End If
    ElseIf aScan(i, 3) Like kho Then
      Call AddRes(dic, res, k, Array(aScan(i, 2), aScan(i, 7), aScan(i, 3)), aScan(i, 8), 6, 1)
    End If
  Next i
  sh.Range("P13:W1000").ClearContents
  sh.Range("P13").Resize(k, 8) = res
End Sub

Private Sub AddRes(dic, res, k, ByVal arr, ByVal Q#, ByVal c&, ByVal DauTong&)
  Dim key$, ik&
  key = Join(arr, "|")
  If dic.exists(key) = False Then
    k = k + 1
    dic.Add key, k
    res(k, 1) = k
    res(k, 2) = arr(0)
    res(k, 3) = arr(1)
    res(k, 4) = arr(2)
  End If
  ik = dic(key)
  res(ik, c) = res(ik, c) + Q
  res(ik, 8) = res(ik, 8) + Q * DauTong
End Sub

 

[hoalv1985]

Anh chị cho em hỏi cách khắc phục lỗi:
Cannot update. Database or object read-only khi chạy câu lệnh sql
Em cảm ơn ạ!

 

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

Rút gọn code lại

Sub XYZ()
  Dim dic As Object, sh As Worksheet, aInve(), aScan(), res()
  Dim srI&, srD&, i&, k&, Q#, dau&
  Const kho$ = "*KHO_004*"
 
  Set sh = ThisWorkbook.ActiveSheet
  aInve = sh.Range("A3:E6").Value
  aScan = sh.Range("G3:O29").Value
  srI = UBound(aInve, 1): srD = UBound(aScan, 1)
  ReDim res(1 To srI + srD * 2, 1 To 8)
  Set dic = CreateObject("Scripting.Dictionary")
  For i = 1 To srI
    If aInve(i, 2) Like kho Then
      Call AddRes(dic, res, k, Array(aInve(i, 1), aInve(i, 3), aInve(i, 2)), aInve(i, 4), 5, 1, 1)
    End If
  Next i
  For i = 1 To srD
    If aScan(i, 4) Like kho Then
      If aScan(i, 1) = "OUT" Then
        Call AddRes(dic, res, k, Array(aScan(i, 2), aScan(i, 7), aScan(i, 4)), aScan(i, 8), 7, 1, -1)
      ElseIf aScan(i, 1) = "IN" Then
        Call AddRes(dic, res, k, Array(aScan(i, 2), aScan(i, 7), aScan(i, 4)), aScan(i, 8), 6, 1, 1)
      Else
        Call AddRes(dic, res, k, Array(aScan(i, 2), aScan(i, 7), aScan(i, 4)), aScan(i, 8), 6, 0, -1)
        Call AddRes(dic, res, k, Array(aScan(i, 2), aScan(i, 7), aScan(i, 4)), aScan(i, 8), 7, 1, 0)
      End If
    ElseIf aScan(i, 3) Like kho Then
      Call AddRes(dic, res, k, Array(aScan(i, 2), aScan(i, 7), aScan(i, 3)), aScan(i, 8), 6, 1, 1)
    End If
  Next i
  sh.Range("P13:W1000").Resize(k, 8).ClearContents
  sh.Range("P13").Resize(k, 8) = res
End Sub

Private Sub AddRes(dic, res, k, ByVal arr, ByVal Q#, ByVal c&, ByVal dau&, ByVal DauTong&)
  Dim key$, ik&
  key = Join(arr, "|")
  If dic.exists(key) = False Then
    k = k + 1
    dic.Add key, k
    res(k, 1) = k
    res(k, 2) = arr(0)
    res(k, 3) = arr(1)
    res(k, 4) = arr(2)
  End If
  ik = dic(key)
  res(ik, c) = res(ik, c) + Q * dau
  res(ik, 8) = res(ik, 8) + Q * DauTong
End Sub

Con cảm ơn bác đã tham gia, bác lại bị chú Mỹ lôi kéo rồi

 

[HieuCD]

Con cảm ơn bác đã tham gia, bác lại bị chú Mỹ lôi kéo rồi

Thấy các code khá dài và khó kiểm soát điều kiện cộng trừ tồn kho nên rút gọn dể nhìn hơn
Code trên dư 1 dòng lệnh đã chỉnh lại