Any SQL query advise for inventory item and set

Question

AVAILABLE TABLE: itm_bundle column represents the item (per piece) included in the bundle).

OBJECTIVE: To create a correct query for an inventory item per piece and per set.

In order to produce an item set, the smallest qty of item piece will the basis:

• Example for Set A: a combination of 1 Apple, 1 Banana and 1 Mango. Since the smallest qty is 4, then Set C is 4.
• Example for Set B: a combination of 1 Banana, 1 Mango and 1 Orange. Since Orange has 0 quantity, then Set B is 0.

MY CODE SO FAR: Display all the available items and get the total count and loop. if item type is Set then query the item under for the item set.

DbName = DatabaseLoc & "DATABASE\MAIN INVENTORY.accdb"
Call OpenDbInventory(DbName)

MYSQL = "SELECT T1.itm_id, T1.itm_name, T1.itm_category, T1.itm_details, T1.itm_um, " & _
        "T1.itm_cost, T1.itm_bundle, NewItmQty FROM ITEM AS T1 " & _
        "LEFT JOIN (SELECT item_id, SUM(itm_qty) AS NewItmQty FROM INVENTORY " & _
        "GROUP BY item_id) AS T2 ON T1.itm_id = T2.item_id " & _
        "WHERE (T1.itm_id LIKE '%" & Replace(.txtISSearch.Text, "'", "''") & "%' " & _
        "OR T1.itm_name LIKE '%" & Replace(.txtISSearch.Text, "'", "''") & "%') " & _
        "ORDER BY T1.itm_name ASC "

Set rs = New ADODB.Recordset
rs.Open MYSQL, dbInventory, adOpenKeyset, adLockReadOnly

If rs.EOF Then
    ISITotPageNum = 0
    ISIRec = 0
    Erase ISIArrData
Else
    RowCount = rs.RecordCount
    j = 1
    ISITotPageNum = Fix(RowCount / 50)
    If RowCount Mod RecPerPage > 0 Then ISITotPageNum = ISITotPageNum + 1
    ISIRec = CLng(RowCount)
    ReDim ISIArrData(1 To ISIRec, 1 To 8)
    Do Until rs.EOF 'Or rs.AbsolutePosition >= (rs.PageSize * ISIPagiPageNum)
        BFound = False
        If rs.Fields("itm_um") = "SET" Then
            GetTempItmBundle = ""
            GetTempFirstAtt = True
            
            
            If IsNull(rs.Fields("itm_bundle")) = True Then
                GetTempLowestQty = 0
            Else
                NewArr = Split(rs.Fields("itm_bundle"), "#")
                x = UBound(NewArr) - LBound(NewArr)
                
                For y = 0 To x
                    NewArrFin = Split(NewArr(y), "|")
                    
                    MYSQL = "SELECT item_id, SUM(itm_qty) AS NewSetQty FROM INVENTORY " & _
                            "WHERE item_id ='" & NewArrFin(0) & "' "
                    
                    MYSQL = MYSQL & "GROUP BY item_id"
                    
                    Set rs1 = New ADODB.Recordset
                    rs1.Open MYSQL, dbInventory, adOpenForwardOnly, adLockReadOnly
                    
                    If rs1.EOF Then
                        If GetTempFirstAtt = True Then
                            GetTempFirstAtt = False
                            GetTempLowestQty = 0
                        Else
                            GetTempLowestQty = 0
                        End If
                    Else
                        If GetTempFirstAtt = True Then
                            GetTempFirstAtt = False
                            GetTempLowestQty = rs1.Fields("NewSetQty")
                        Else
                            If rs1.Fields("NewSetQty") < GetTempLowestQty Then
                                GetTempLowestQty = rs1.Fields("NewSetQty")
                            End If
                        End If
                    End If
                    
                    rs1.Close
                    Set rs1 = Nothing
                    
                Next y
            End If

        
                TempItmCtr = TempItmCtr + 1
                ISIArrData(TempItmCtr, 1) = rs.Fields("itm_id")
                ISIArrData(TempItmCtr, 2) = Replace(rs.Fields("itm_name"), "''", "'")
                ISIArrData(TempItmCtr, 3) = FormatNumber(rs.Fields("itm_cost"), 2, , vbTrue)
                    
                ISIArrData(TempItmCtr, 4) = FormatNumber(GetTempLowestQty, 0, , vbTrue)
                TempTotQty = TempTotQty + GetTempLowestQty
                
                ISIArrData(TempItmCtr, 5) = Replace(rs.Fields("itm_category"), "''", "'")
                If IsNull(rs.Fields("itm_details")) = False Then
                    ISIArrData(TempItmCtr, 6) = Replace(rs.Fields("itm_details"), "''", "'")
                End If
                ISIArrData(TempItmCtr, 7) = rs.Fields("itm_um")
                
                If IsNull(rs.Fields("itm_bundle")) = False Then
                    ISIArrData(TempItmCtr, 8) = rs.Fields("itm_bundle")
                End If
                
                    
        Else
        
                TempItmCtr = TempItmCtr + 1
                ISIArrData(TempItmCtr, 1) = rs.Fields("itm_id")
                ISIArrData(TempItmCtr, 2) = Replace(rs.Fields("itm_name"), "''", "'")
                ISIArrData(TempItmCtr, 3) = FormatNumber(rs.Fields("itm_cost"), 2, , vbTrue)
                    
                If IsNull(rs.Fields("NewItmQty")) = True Then
                    ISIArrData(TempItmCtr, 4) = 0
                Else
                    ISIArrData(TempItmCtr, 4) = FormatNumber(rs.Fields("NewItmQty"), 0, , vbTrue)
                    TempTotQty = TempTotQty + rs.Fields("NewItmQty")
                End If
                
                ISIArrData(TempItmCtr, 5) = Replace(rs.Fields("itm_category"), "''", "'")
                If IsNull(rs.Fields("itm_details")) = False Then
                    ISIArrData(TempItmCtr, 6) = Replace(rs.Fields("itm_details"), "''", "'")
                End If
                ISIArrData(TempItmCtr, 7) = rs.Fields("itm_um")
        
                If IsNull(rs.Fields("itm_bundle")) = False Then
                    ISIArrData(TempItmCtr, 8) = rs.Fields("itm_bundle")
                End If
                
        End If
        j = j + 1
        rs.MoveNext
    Loop


End If

Set li = Nothing

rs.Close 'CLOSE RECORDSET
Set rs = Nothing

dbInventory.Close 'CLOSE DATABASE
Set dbInventory = Nothing

QUESTION: Is there anyway i can improve my query? Thank you.