Results 1 to 5 of 5
  1. #1
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119

    How to design a product inventory management database

    I have a database with the following table:
    Products - Product_Orders - Suppliers - Customers - Sales - Sales_items.

    I want to keep products inventory in the following manner:

    1 - When we order for a product, then the quantity ordered is added to the initial quantity in stock of that product.

    2 - When we sale a product, the quantity sold is deducted from the initial quantity in stock of that product.

    3 - When we reach a minimum stock quantity for a product, then that product is removed from the list of products available for sale.

    Questions:
    - What are the required tables to achieve the above ?
    - What will be the columns of those tables?


    - What will the relationships be like?

    I am planning to use forms to insert records, so there will definitely be a front end programming. But for now I need to understand the structure of the database before anything else.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    maintaining a stock balance field is a bad idea - it can easily go wrong.

    Better to calculate stock in a query or using dsum on a form when required. To make this easier, combine your purchase and sales tables into one transactions table with an additional field to indicate type of transaction (or perhaps have two 'parent' fields, one for product orders and one for customer sales and only one completed per transaction). This has the additional benefit that you can add additional stock movement transactions - adjustments, stocktake, writedowns, reserves, etc. Calculation for current stock would be to sum all transactions from latest stocktake to today.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The answer to your 3 questions is: it depends on your requirements.

    Look at this site http://www.databaseanswers.org/data_models/index.htm
    In the center column, go down to the header 7. Inventory. There are 14 examples..



    Also see this article by Allen Browne: http://allenbrowne.com/AppInventory.html

  4. #4
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    Thanks Guys. I will explore your various suggestions and post a reply.

  5. #5
    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
    I recommend 3 videos by Daniel Soper.
    There are # 1,2 and 4 in the Database series that starts here.

    Work through
    this free tutorial from RogersAccessLibrary to get first hand experience in a designing tables and relationships.
    It will take 30-45 minutes, but you will learn a process that can be used with any database. You have to work through it to get the experience.

    Here's a link to a free StockManagement video. You may get some ideas and insight from this.

    Bottom line:
    Write clear description of the business you are trying to automate.
    Create a data model. (pencil and paper is fine)
    Get/create some test data.
    Verify/validate your data model. Reconcile every anomaly. Then retest.

    See My stump the model info .

    Good luck

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

Similar Threads

  1. Replies: 10
    Last Post: 01-06-2015, 12:37 PM
  2. Replies: 3
    Last Post: 06-26-2014, 10:08 PM
  3. Replies: 12
    Last Post: 06-06-2014, 01:25 PM
  4. Inventory Management Design
    By dylcon in forum Access
    Replies: 6
    Last Post: 10-07-2013, 12:29 AM
  5. Access Well Customized Inventory management Database
    By itzmemike in forum Programming
    Replies: 5
    Last Post: 04-20-2012, 02:32 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