Results 1 to 6 of 6
  1. #1
    Sniper1401 is offline Novice
    Windows 8 Access 2007
    Join Date
    Feb 2019
    Posts
    3

    how to design a dbase for warehouse inventory with multiples warehouses and internal transfers


    Hello I want to design a dbase to store inventory quantity of my products. I want to be able to add and substract quantities. I will create products.tbl order.tbl and sales.tbl tables. My problem is that I have two warehouses (two locations), and I also want to make internal trasfers, both from one warehouse to the other, as well inside the warehouse (the warehouse is divided into areas so for instance I have 2 in area one and 3 in area two). The point is to be able in a form to see the abailable quantity of an item and exactly where this quantityt is (in which warehouse, in which area etc.)

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What have you tried so far?There are several posts in several forums dealing with Inventory/Stock Control.

    See http://www.databaseanswers.org/data_...ment/index.htm
    and https://www.youtube.com/watch?v=9HRmSRR29fY
    and http://allenbrowne.com/AppInventory.html for ideas.

  3. #3
    Sniper1401 is offline Novice
    Windows 8 Access 2007
    Join Date
    Feb 2019
    Posts
    3

    trying to find way to sum total quantity for each productID at each specific LoacationID

    Quote Originally Posted by orange View Post
    What have you tried so far?There are several posts in several forums dealing with Inventory/Stock Control.

    See http://www.databaseanswers.org/data_...ment/index.htm
    and https://www.youtube.com/watch?v=9HRmSRR29fY
    and http://allenbrowne.com/AppInventory.html for ideas.
    Click image for larger version. 

Name:	schema_tryouts.jpg 
Views:	26 
Size:	114.5 KB 
ID:	37395

    So far I have created the product table, and product details table, have created a locations table (with the different locations or bins I will be storing goods). So when I buy something or add something I do it directly. I buy x productID , x quantity, and at x location. The same when I sell products. With a query with "group" I can see how many items I have added for same code that exist in the same bin and sum the total. The same for the items I have sold. how could I create a form that lets me select the product code and below show me the actual inventory that exists in all the bins that the goods are stored (I do not want it to show all the bins where there is no value)

    the other idea is that I make a transcaction type table 1= buy/add 2= sell/deduct and when I use any invoice or transfer goods type I have a field that declares if it is a buy or sell transcation. but somehow I want to be able to sum for the specific prooductID all the entries of the invoices that have the "add" transaction for each of the storage place. the same for deduction and again I need the totals for each product id and for each storage space. Can someone help?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 3 was moderated, I'm posting to trigger email notifications. I deleted the duplicate moderated post.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    For applications like this (where you have to follow movements in/out for different storage units, and to calculate various balances), the transactions table is a must. A possible database structure on fly:

    tblProducts: ProductID, ProductCode, ProductName, ProductUnit, ...;
    tblStorages: StorageID, StorageName, ...
    tblProductMovements: MovementID, MovementType, MovementDate, MovementOrderNo, ProductID, StorageID, MovedQty, UnitTradePrice, ... (Where MovementType has separate values for incoming purchased products, outgoing sold products, movement between storages, writing of products because nonconformity, writing of products because overaging, correcting entries, etc. MovementOrderNo is used for purchase/selling movements and may be used as foreign key to link with Orders table(s). UnitTradePrice price is also for purchase/selling movements, as you can't have those prices hard-coded into database, or even into some table - they may change at every transaction, and often you don't have any control over them.);
    As you probably need to calculate the value of products in any of storages or in total, and maybe also when moving products between storages, or between storage and production, generally for any internal movement, you also need a table where the prices used for this purpose are stored:
    tblStoragePrices: StoragePriceID, ProductID, UnitStoragePrice, ValidFrom. (The cost of internal movement or the value of product in storage is calculated based on fixed date, and the valid price for this date is read from tblStoragePrices.)

    The quantity of given product in given storage is calculated as sum of all incoming quantities minus sum of all outgoing quantities. No need to save it anywhere - in this way you'll have less headaches in future.

    To avoid calculating storage balances over long time period, you can make e.g. yearly inventories, and save the results in separate inventory table:
    tblInventories: InventoryID, InventoryDate, StorageID, ProductID, InventoryQty;

    At end of inventory, the balance of all product quantities in storage is compared with inventory quantity, and for any differences correction entries are made for storage. After that you can calculate product quantities in storage at any date as the inventory quantity at last inventory before this date plus all incomings between inventory date and report date minus all outgoings between inventory date and report date.

  6. #6
    Sniper1401 is offline Novice
    Windows 8 Access 2007
    Join Date
    Feb 2019
    Posts
    3
    Quote Originally Posted by ArviLaanemets View Post
    For applications like this (where you have to follow movements in/out for different storage units, and to calculate various balances), the transactions table is a must. A possible database structure on fly:

    tblProducts: ProductID, ProductCode, ProductName, ProductUnit, ...;
    tblStorages: StorageID, StorageName, ...
    tblProductMovements: MovementID, MovementType, MovementDate, MovementOrderNo, ProductID, StorageID, MovedQty, UnitTradePrice, ... (Where MovementType has separate values for incoming purchased products, outgoing sold products, movement between storages, writing of products because nonconformity, writing of products because overaging, correcting entries, etc. MovementOrderNo is used for purchase/selling movements and may be used as foreign key to link with Orders table(s). UnitTradePrice price is also for purchase/selling movements, as you can't have those prices hard-coded into database, or even into some table - they may change at every transaction, and often you don't have any control over them.);
    As you probably need to calculate the value of products in any of storages or in total, and maybe also when moving products between storages, or between storage and production, generally for any internal movement, you also need a table where the prices used for this purpose are stored:
    tblStoragePrices: StoragePriceID, ProductID, UnitStoragePrice, ValidFrom. (The cost of internal movement or the value of product in storage is calculated based on fixed date, and the valid price for this date is read from tblStoragePrices.)

    The quantity of given product in given storage is calculated as sum of all incoming quantities minus sum of all outgoing quantities. No need to save it anywhere - in this way you'll have less headaches in future.

    To avoid calculating storage balances over long time period, you can make e.g. yearly inventories, and save the results in separate inventory table:
    tblInventories: InventoryID, InventoryDate, StorageID, ProductID, InventoryQty;

    At end of inventory, the balance of all product quantities in storage is compared with inventory quantity, and for any differences correction entries are made for storage. After that you can calculate product quantities in storage at any date as the inventory quantity at last inventory before this date plus all incomings between inventory date and report date minus all outgoings between inventory date and report date.
    Thanks Avril I will give it a go and get back to you

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

Similar Threads

  1. Inventory Management - Warehouse locations
    By jharper in forum Database Design
    Replies: 1
    Last Post: 05-28-2014, 06:20 AM
  2. Warehouse and Inventory Template
    By miguel.escobar in forum Access
    Replies: 0
    Last Post: 06-12-2012, 12:41 PM
  3. Calculating Current Stock in Inventory/Warehouse
    By yohansetiawan in forum Access
    Replies: 5
    Last Post: 03-14-2012, 09:05 PM
  4. Query Design help for Food Pantry dbase
    By gr8rishe04 in forum Queries
    Replies: 4
    Last Post: 11-16-2010, 03:20 PM
  5. Inventory/Warehouse Control
    By Maker in forum Access
    Replies: 3
    Last Post: 09-01-2010, 10:46 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