Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2013
    Posts
    5

    Question Unanswered: correlated query to find cheapest manufacturer for a product

    Hi,

    I have a SQL Query that returns me:
    - the "product code" i must purchase,
    - the "quantity" to purchase for this product
    - and if it's in "promotion or not".

    Based on this result, i must to look at "manufacturer" table and find the cheapest "price" for each product to purchase and the quantity available at the manufacturer.
    If "quantity to order" (e.g. 20) is higher than "quantity available" (e.g. 12) at "cheapest manufacturer", i must order the missing quantity (8) to the next cheapest manufacturer.
    How can i do that ?

    thx

  2. #2
    Join Date
    Mar 2015
    Posts
    27
    What does your manufacturer table look like? Got to have more information to help you with this. Field names, my good man... field names.

  3. #3
    Join Date
    Feb 2013
    Posts
    5
    Ok so to make you understand a little bit better.

    Here is the result of my previous Query: http://prntscr.com/6m0hin
    so you can see what are the product code to order, their respective quantity and if they are promotional or not.

    and this is the supplier/manufacturer prod table
    http://prntscr.com/6m0i85

    for each product i need to purchase:
    1. if product i not promotional, i must order the full quantity to the cheapest manufacturer. So in my case for MON003, i must order 16 to IB for $225
    2. for promotional products, i must firstly check if the cheapest manufacturer has enough prod in stock. If not, i order all the prod in stock the manufacturer has, and i must order to missing ones to the next cheapest manufacturer.
    So in my case for DTP1002 i must order 5 prod to HP for $700 and next 15 prod to IB for $800

    this is a business rule that gives me headache

  4. #4
    Join Date
    Mar 2015
    Posts
    27
    Just one question before I post my solution.... what if the manufacturer who has it cheapest has 0 in stock, do you still place the order with them if it is non-promotional?

  5. #5
    Join Date
    Mar 2015
    Posts
    27
    OK, well here is a solution in VBA. It places an order with the manufacturer with the cheapest price for a product not on promo, whether or not a vendor has any of it in stock. If it is a promo, it places orders with the vendors who have it in stock going from cheapest to most expensive and lets you know if the order couldn't be completely filled. Note: it doesn't actually place any orders, just calls a sub where you can put that code but.... You should change the table and query names to match your actual situation but the code is tested against your sample data and does work. Good luck with your project.
    Code:
    Public Sub ProcessOrders()
        Dim db As DAO.Database
        Dim rs1 As DAO.Recordset
        Dim rs2 As DAO.Recordset
        Dim intTotalProductNeeded As Integer
        Dim intUnordered As Integer
        Dim intQtyToOrder As Integer
        Dim strProductsQuery As String
        
    
        Set db = CurrentDb
        
    
        '* get the products to order list
        '* this query name should be changed to whatever you named the query that returns the orders table you described
        '* my version just returns the values you supplied in your example from a test table
        Set rs1 = db.OpenRecordset("qryProductsToOrder")
        If rs1.RecordCount > 0 Then
            rs1.MoveFirst
            Do While Not rs1.EOF
            
    
                '* table tblProducts is your manufacturers/products table, change it's name to reflect reality in your situation
                If Nz(rs1![Promotional Product], "No") <> "Yes" Then
                            strProductsQuery = "SELECT TOP 1 * FROM tblProducts " & _
                                        "WHERE S_PROD_CODE = '" & rs1![Product Code] & "' " & _
                                        "ORDER BY PROD_COST ASC"
                
    
                    Set rs2 = db.OpenRecordset(strProductsQuery, , dbPessimistic)
                    
    
                    If rs2.RecordCount > 0 Then
                        '* if we found any manufacturers who carry this product, in stock or not,
                        '* lets place an order with the one with the cheapest price
                        rs2.MoveFirst
                        intTotalProductNeeded = rs1![Quantity To Order]
                        'intUnordered = intTotalProductNeeded
                        Call PlaceOrders(rs2![S_PROD_CODE], rs2![S_MANU_CODE], intTotalProductNeeded)
                   End If
                   rs2.Close
                   
    
                Else
                   
    
                    '* get all product records from the manufacturers who have at least 1 in stock,
                    '* ordered by the cheapest to most expensive
                    strProductsQuery = "SELECT * FROM tblProducts " & _
                                        "WHERE S_PROD_CODE = '" & rs1![Product Code] & "' " & _
                                        "AND NZ(SUPP_STOCK,0) > 0 ORDER BY  PROD_COST ASC;"
                    Set rs2 = db.OpenRecordset(strProductsQuery, , dbPessimistic)
                    '* if we found any manufacturers who have this product in stock, lets try to fill the order
                    If rs2.RecordCount > 0 Then
                        rs2.MoveFirst
                        intTotalProductNeeded = rs1![Quantity To Order]
                        intUnordered = intTotalProductNeeded
                        Do While Not rs2.EOF
                        
    
                            If intUnordered > 0 Then
                                intQtyToOrder = IIf(rs2![SUPP_STOCK] >= intUnordered, intUnordered, rs2![SUPP_STOCK])
                                '* call the routine to place the order and pass it the product code, manufacturer,
                                '* and the qty which is equal to or less than the manufacturers stock level
                                If PlaceOrders(rs2![S_PROD_CODE], rs2![S_MANU_CODE], intQtyToOrder) Then
                                    '* if the order waa successfully placed, decrement the stock level by the qty ordered
                                    '* if this table is a local db and not from some live query source then you may need to decrement
                                    '* the stock levels, if so uncomment the next 3 lines
                                    'rs2.Edit
                                    'rs2![SUPP_STOCK] = rs2![SUPP_STOCK] - intQtyToOrder
                                    'rs2.Update
                                    intUnordered = intUnordered - intQtyToOrder
                                End If
                            Else
                                '* if the order is filled completely, exit the purchase loop
                                Exit Do
                            End If
                            rs2.MoveNext
                        Loop
    
    
                    End If
                    rs2.Close
                    
    
                    '* when we get here we have filled the order completly or run out of suppliers who have any
                    If intUnordered > 0 Then
                        '* notify the user that the product is only partially (or not all all) filled
                        Call UnableToFillOrderCompletely(rs1![Product Code], intTotalProductNeeded, intUnordered)
                    End If
                    
    
                End If
                
    
                '* Get next product to order
                rs1.MoveNext
                
    
            Loop
        
    
        End If
        
    
        '* we are done processing the orders
        rs1.Close
        Set rs2 = Nothing
        Set rs1 = Nothing
        Set db = Nothing
                        
    
    End Sub
    
    
    Private Function PlaceOrders(ProdID As String, ManID As String, QtyNeeded As Integer) As Boolean
        '* By the end of this function this variable should be set true of false
        '* indicating the order was placed successfully or not
        Dim OrderPlacedSuccessfully As Boolean
        
    
        '* code whatever you need here to place an order with this ManID for QtyNeeded of ProdID
        OrderPlacedSuccessfully = True
        MsgBox "Order placed for " & QtyNeeded & " of product " & ProdID & " from " & ManID, vbInformation + vbOKOnly
        
    
        PlaceOrders = OrderPlacedSuccessfully
    
    
    End Function
    
    
    Private Sub UnableToFillOrderCompletely(ProdID As String, intTotalQtyNeeded As Integer, intQtyUnordered As Integer)
        
    
        '* Write whatever code you need here to let user know than you couldn't completely fill the order
        MsgBox "Unable to completely fill order for " & intTotalQtyNeeded & " of " & ProdID & ". " & intQtyUnordered & " remain unordered.", vbCritical + vbOKOnly
        
    
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •