Results 1 to 5 of 5
  1. #1
    USMCPelto is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    3

    Inventory Across Multiple Locations or Warehouses

    Afternoon,



    While laying out the groundwork, the basic function of this database is to track 21 items. All items will be received in the main warehouse or shop and then sent out in smaller batches to 8-10 different jobsites which will all maintain a smaller count/inventory on hand. For table design, would it more normalized to list each location on a locations table, or would another column on the parts tables be preferred for storing quantity?

    Example, for PartA, we may have 30 at the main warehouse, but would then need to transfer 5 units of PartA to Location2. We would still have 30 PartA's, but there should show 25 at the main warehouse and 5 at Location2. I'm mostly in the planning stages, but am wanting to set up the foundation correctly for future growth.

    The ultimate goal is to track each location's usage on a weekly basis of these 21 items. All items purchased will initially go to the main Warehouse, that we'll call Location1. From there, they will then be transferred in smaller batches to replenish stock as used across the 8-10 jobsites.

    Most templates and examples deal with customers, but this is less of a sale and is a transfer.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I've built something similar for a client - but for about 10,000 products and 100 locations. Another factor to consider is goods in transit - i.e. goods have left one warehouse but not yet arrived at it's destination. This will matter for stock take, stock valuation and stock adjustments.

    You also need to consider how you handle products received into Location1 and how products are sold/used in the other locations

    There are two ways to handle it - which is best for you will depend on your processes and anything else you might need by way of reporting

    You'll have a table for products and a table for locations. You then need a joining table which might look like this

    tblMovements
    MovementPK
    ProductFK
    LocationFK
    MovementType (i.e. dispatch or receipt)
    Quantity
    MovementDate

    for each movement you need two records, one for the despatch and one for the receipt


    alternatively you might have

    tblMovements
    MovementPK
    ProductFK
    DespatchLocationFK
    ReceiptLocationFK
    Quantity
    MovementDate


    this only requires one record per movement

    In both cases, quantity can be determined by summing the amount by product

    These are not the only basis's but other ways would work in much the same way

    You need to ask yourself some 'what if' questions - may or may not be relevant to your situation

    what if 5 items are dispatched, but only 4 received - or 6? - or none?
    what if some items are received, but have not been dispatched? perhaps because it was dispatched as 1 product, but received as another because someone mistyped a code
    what if location2 needs a product in a hurry and gets one from location3 (perhaps location1 has not stock at the time)
    you mention jobsites, which implies to me they are not permanent - so what if location2 closes and products are returned to location1

  3. #3
    USMCPelto is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    3
    Quote Originally Posted by Ajax View Post

    You also need to consider how you handle products received into Location1 and how products are sold/used in the other locations

    You need to ask yourself some 'what if' questions - may or may not be relevant to your situation

    what if 5 items are dispatched, but only 4 received - or 6? - or none?
    what if some items are received, but have not been dispatched? perhaps because it was dispatched as 1 product, but received as another because someone mistyped a code
    what if location2 needs a product in a hurry and gets one from location3 (perhaps location1 has not stock at the time)
    you mention jobsites, which implies to me they are not permanent - so what if location2 closes and products are returned to location1
    --------------------------------

    You also need to consider how you handle products received into Location1 and how products are sold/used in the other locations
    Products will never be "sold" from Location1, but will be used/consumed on all other locations. Fortunately, I'll be going over daily tickets/work orders and would be entering the amount used on a once per week, per jobsite order/use report. These are parts that get used up/washed out as the job goes on and will be added up and billed weekly to the customer who's jobsite they were used on.

    what if 5 items are dispatched, but only 4 received - or 6? - or none?
    The mismatched quantities shouldn't be an issue, due to the small scale, and should result in a person on the jobsite updating their daily ticket to represent what was used if there is a discrepancy. An inaccuracy beyond that implies theft, which can also be accounted for via adjustment.

    what if some items are received, but have not been dispatched? perhaps because it was dispatched as 1 product, but received as another because someone mistyped a code
    what if location2 needs a product in a hurry and gets one from location3 (perhaps location1 has not stock at the time)

    The movement of assets will be done in the main office, Location1. Parts can be moved around from location to location, but would either be relayed back to the main office or would be discovered when the weekly parts run notices a discrepancy. We'll be delivering parts once per week, while picking up the used up ones and conducting a quick inventory/count while onsite.

    you mention jobsites, which implies to me they are not permanent - so what if location2 closes and products are returned to location1
    These are indeed temporary locations. They tend to last about 5-8 months at a time and any unused parts at the end will indeed come back into stock at Location1, the main office.


    For the time being, the database will not be used on the jobsites but instead will be ran from a computer at the main office.


    I appreciate you taking the time to answer.

  4. #4
    USMCPelto is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    3
    As pertinent, here's what it's currently shaping up to be:

    tblTransactions
    TransactionID(PK)
    Product(FK)
    DispatchLocation(FK)
    ReceivedLocation(FK)
    Quantity
    Date

    To expand on that design, if I'm sending 5 different items, then there will be 5 lines added to the Transactions table, correct? If I then want to know how many of Product1 are on a jobsite, I'd run a query to add up all transactions positive and negative and return a resulting sum?

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    If I then want to know how many of Product1 are on a jobsite, I'd run a query to add up all transactions positive and negative and return a resulting sum?
    with a criteria for the receiptlocation being the jobsite, yes

    note date is a reserved word, not recommended to use it as a field name - see this link https://support.office.com/en-gb/art...9-f855bdd9c5a2

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

Similar Threads

  1. Replies: 5
    Last Post: 02-13-2019, 09:43 AM
  2. Inventory Management - Warehouse locations
    By jharper in forum Database Design
    Replies: 1
    Last Post: 05-28-2014, 06:20 AM
  3. Tracking Inventory At Multiple Locations
    By Dustin in forum Database Design
    Replies: 4
    Last Post: 12-28-2013, 10:32 PM
  4. Replies: 1
    Last Post: 06-17-2012, 12:38 AM
  5. Replies: 0
    Last Post: 01-26-2011, 05:51 PM

Tags for this Thread

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