Results 1 to 7 of 7
  1. #1
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114

    Allocating Inventory

    I have a view that I am unable to edit that holds part numbers and quantities. I then import a .xlsx into the database that holds part numbers and quantites requested and I need to compare that to the view. Simple steps here...this is the tricky part for me. Once an items quantity in inventory has been allocated to an item from the spreadsheet, I need to reduce X from the inventory quantity.

    So let's say that the view (which I can not edit) looks like this


    Item Qty
    RSks 12
    BSks 10
    GSks 8

    And the spreadsheet import looks like this
    User Item Request
    1 RSks 10
    2 BSKS 5
    3 RSks 6

    So in the below scenario User 1 would get 10 RSks leaving only 2 in inventory, so when user 3 is hit we would be short 3. What would be the most efficient way of updating counts like this on a one by one basis so that inventory is not allocated twice?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Typically you would have some record of current inventory in your database.
    Any imports or exports would be considered transactions and would adjust inventory.
    The transactions would be implemented as queries against current inventory.
    Imports would be additions. Exports or usage or removal would be considered subtractions from current inventory.

    New Inventory Counts = Current InventoryCounts + Additions - Subtractions

  3. #3
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    @orange ->

    This process will only be utilized for inventory reductions. This may be a horrid implementation, but what I was thinking was since I am unable to edit the view do something like this
    1) Make Local Access Table of the view
    2) Using VBA Query 1st Item in Excel Import Table Against Local Access Table Of View (Maybe use DCount() so I can store the count in a variable)
    3) Deduct that count from the Local Access Table Item
    4) Query 2nd Item in Excel since the count has been updated.


    My fear/concern/worry is that this could become very cumbersome and utlize a great deal of resources if the number of items in the excel import is high.

  4. #4
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Quote Originally Posted by orange View Post
    New Inventory Counts = Current InventoryCounts + Additions - Subtractions
    There would not be any additions so I would just need to do InventoryCounts - RequstedCounts but on a user by user basis. That is where I am hung up, updating the request sheet on a user by user basis.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tell us more -especially about "on a user by user basis"
    which you didn't mention earlier.

    you would import into a transaction table, or link to the excel file depending on requirements.

    Run the transaction table against your existing inventory.

    But the devil is in the details.

  6. #6
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Sorry, I thought I explained it in the beginning...if you look at my OP
    So let's say that the view (which I can not edit) looks like this
    Item Qty
    RSks 12
    BSks 10
    GSks 8

    And the spreadsheet import looks like this
    User Item Request
    1 RSks 10
    2 BSKS 5
    3 RSks 6

    So still utilizing the above example, I would want to take row 1 of the spreadsheet (which I will import into an access table) - and compare that to the SQL View
    So take RSks 10 and check against RSks 12 - so I would now want the View to be updated (altho I know you can not update the view) with RSks 2 (because 12 - 10 = 2)

    Essentially updating the View Count after each row in the spreadsheet. Does that help clarify?

    EDIT -->
    Essentially query row 1 against the view, update the view, query row 2 against the view, update the view and so on and so forth until all rows in the spreadsheet have been checked against the view

  7. #7
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Something like this is what I was envisioning and there is probably a better way to achieve, but using two local access tables for the View (so we can maniuplate the data) and for the excel spreadsheet (Imported into a table)

    Pseudocode
    Code:
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Select user, item from localtable")
    
    
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do Until rs.EOF = True
            user = rs!User
            item = rs!Item
            
            recordcount = DCount("QTY", "LocalTable", "Item = " & item)
            
            Update spreadsheettable
            Set InventoryQTY = recordcount
            Where item = & item
            And user = user
    
            Update localtable
            Set QTY = QTY - recordcount
        Loop
    Else
        'Do Nothing bc we don't care
    End If
    MsgBox "All records processed"
    
    
    rs.Close
    Set rs = Nothing

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help allocating Primary/Foreign Keys and Relationships
    By rosscortb in forum Database Design
    Replies: 2
    Last Post: 07-08-2015, 08:12 AM
  2. Allocating criteria to query with VBA
    By gangel in forum Queries
    Replies: 1
    Last Post: 02-19-2015, 08:18 AM
  3. allocating products into boxes
    By Pacific1 in forum Queries
    Replies: 28
    Last Post: 12-08-2014, 10:04 PM
  4. Replies: 1
    Last Post: 11-05-2014, 11:08 AM
  5. Replies: 1
    Last Post: 02-21-2013, 12:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums