Results 1 to 3 of 3

Database to Manage Supplies Inventory

  1. #1
    Chris2810 is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2016
    Location
    Missouri, United States
    Posts
    9

    Database to Manage Supplies Inventory

    Hello all. I am working on a new database to help keep track of supplies.



    So far, I have two tables:

    - an Items table which contains all of the information for each item (the fields are ItemID (PK), ItemDescription, ItemCategory....etc)

    - a Transactions table which contains all of the information about transactions (the fields are TransactionID (PK), ItemID (FK), Quantity, Units, Originating location, Date sent, Date arrived, Final location, etc)

    We have several stores that will send supplies to our warehouse, and we can send supplies from the warehouse to the different stores. The database just needs to keep track of how much is sent from one location to the other and how much we have on hand. All of that can be determined from the transactions (we know that if we get 5 units in the warehouse, but send 2 to store A, we have 3 left on hand at the warehouse).

    A form will be used to enter transactions. The problem is we are expecting a large amount of new items to be coming into the warehouse. These items are not currently in the items table. I don't want the user to have to enter each item into the items table and then have to also use the form to enter the transactions for those items. It would split the data-entry process in half if the form could enter data into both tables simultaneously. We want the user to be able to enter the information into one form and have Access enter the transaction information into the transaction table, but also put the new items into the items table. If the item was already in the table, we wouldn't want Access to enter it into the items table (causing duplicates).

    I've read on other forums that its supposed to be one form to one table. Meaning my form can only enter data into my transactions table and not into the items table.

    Does anyone know of an alternative solution? Any ideas are appreciated.

    Thanks,

    Chris

  2. #2
    alansidman's Avatar
    alansidman is offline Life @ 7100'
    Windows 10 Access 2013
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,436
    Look into using sub forms. These two links will help you to understand and build.

    http://www.datapigtechnologies.com/f...subforms1.html

    http://www.datapigtechnologies.com/f.../subform2.html

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,343
    You may get some ideas from this youtube video on Stock Management. This may give some real world advice and insight into Inventory/Stock management. It may be useful to see a similar application and how others have approached certain situations.

    Good luck.

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

Similar Threads

  1. Law Firm Database - How to manage people and cases
    By Mersad in forum Database Design
    Replies: 28
    Last Post: 08-06-2015, 06:15 PM
  2. Office Supplies to Employee (Inventory)
    By otis in forum Database Design
    Replies: 21
    Last Post: 03-09-2015, 03:56 AM
  3. Office Supplies to Employee (Inventory)
    By otis in forum Database Design
    Replies: 7
    Last Post: 03-07-2015, 12:10 PM
  4. How to manage the manage the input of data?
    By Gambit17 in forum Import/Export Data
    Replies: 4
    Last Post: 07-30-2013, 10:32 AM
  5. Replies: 6
    Last Post: 06-26-2011, 03:13 PM

Posting Permissions

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