Database to Manage Supplies Inventory

[Chris2810]

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

[alansidman]

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

[orange]

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.