Solved SQL query advise for inventory item and set (1 Viewer)

[atzdgreat]

AVAILABLE ITEM: 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.

 

[mike60smart]

Can you show us a screen print of your Relationship window displaying all tables?

 

[atzdgreat]

Can you show us a screen print of your Relationship window displaying all tables?

hi @mike60smart thank you for your reply. i already updated my post.

 

[gemma-the-husky]

I don't like the mvf

I would probably have a separate itemsets table, where you have

Set Name, ItemIncluded, Qty
Set A, 1, 1
Set A, 2, 1
Set A, 3, 4
Set B, 1, 1
Set B, 3, 2
Set B, 4, 1

then it's easier to find the sets in which a given item is used.

I don't think you would store sets as a stock item - unless they are are pre-packaged, in which case you probably wouldn't want to break a set to retrieve an item, and the Set becomes atomic, if you will.

It's more that then you want to pick a given Set, the system tells you which items to pick. It also makes it easier if your set includes multiples of some items. eg, in the above Set A needs 4 x item 3, but set B needs 2 of that item.

It depends how your business actually operates to be honest.

 

[ebs17]

Is there anyway i can improve my query?

Correctly planned table structures often enable simpler to simple queries, unfavorable structures require more effort and all sorts of contortions.

Your example looks like a very badly modeled down BOM (bill of materials). Tables and relationships would therefore probably use this data model.

Your actual concern is not yet clear to me:
- The stock would be determined at a point in time from the sum of positive movements (arrivals) and negative movements (departures). For this you need a corresponding movement table.

Do you want to divide all available items into a specific set and determine the number of possible sets?
Do you want to determine the total number of all items (single items and in sets)?

What do you want?

 

[jdraw]

atzdgreat,
I suggest you describe and differentiate piece, item, set and bundle to clarify what it is you are talking about.
What exactly do you mean by "improve"?
You could post a copy of your database with instructions and, as Ebs17 suggested, tell us exactly what you want as output. Your requirement might be clearer if you gave it to us in a business related rationale.

 

[atzdgreat]

Hi @ebs17 and @jdraw thank you for your reply and my apology for my post was not clear. with @ebs17 both question, its Yes. as an example to my post, let say the vendor has 4 pcs. of apple, 6 pcs of Banana, 4pcs of Mango and 0 orange. these are the available items in the inventory. now the vendor wants to create a bundle which compose of assorted fruits. For example Set/Bundle A compose of 1pc of Apple, 1pc of Banana and 1pc of Mango. If the vendor wants to view his/her inventory stock, he/she will see Set/Bundle A has a qty of 4 aside from the item(s) the he/she has,

How does Set/Bundle A got 4 qty?
since the bundle only requires 1pc each for Apple, Banana and Mango. the lowest qty of the item will be the basis.

What if there are some movement from the item?
for example if there is a pc of Apple has been sold out and the remaining is 3. So the Set/Bundle A will be affected also and it will become 3.
and again there is a delivery of 2pc Apple and 1pc Mango. the current total of fruits: 3+2 Apple, 6 Banana, 4+1 Mango. Now Set/Bundle A will become 5 since the lowest item is already 5.

Set/Bundle will be affected once there are some changes in the items under to the said bundle.

What if Set/Bundle has movement?
for example if the vendor sold 2 Set/Bundle, automatically the item under to the said bundle will automatically deducted. which the new total available: 3 Apple, 4 Banana and 3 Mango.

What would be the new output?
3 Apple
4 Banana
3 Mango
0 Orange
3 Set/Bundle A

 

[ebs17]

First approach:
1) changed tables => database schema
2) Solution in one query

 

[atzdgreat]

First approach:
1) changed tables => database schema
2) Solution in one query

hi @ebs17 my apology for not getting back to you sooner. i will now check today and get back to you soonest. advance blessed new to all of you. thank you.

 

[atzdgreat]

hi @ebs17 i already saw your sample. thank you so much for this. this is a very big help for me.