The best advice I can offer you is to purchase an Accounting package. There is a lot under the hood of an Accounting application that is easily over looked and that isn't fully realized until you get in and start messing around with it. Stock level is one aspect that can be a bit daunting, but there are at least a dozen different ways to cost an Item. You may only see the need for one, but sooner or later you or someone else will find a need to cost an item differently and if this isn't planned for in the beginning, you will end up rewriting a lot of your code and queries. And costing is just one of the many gotchas. Serial tracking and Lot tracking can be a barrel of fun.
But if you want to see what you can accomplish, I would recommend:
- Create an InventoryItem Table to store Inventory Item Definitions. Things like Item Number, Costing type, Tracking Type, Unit of Measure, Active.
- Create a WarehouseLocation Table with address information (or a lookup to an address record) Stocking type, like Stock or non-stock.
- Create a ItemWarehouse table to link Items to Warehouses and Quantities on Hand.
- Create an InventoryTransaction Table to store transactions like moving some of an Item from one warehouse to another. This is where things can get really messy because if you are doing any form of accounting of costs, activity in this table will directly relate to your Accounts Receivable and Accounts Payable, and they will need to match up.
If you are costing:
- Create a Customer/Vendors Table to link to an address and set credit limits.
- Create an Customer Orders Table to hold information about a customer order.
- Create a Customer Orders Lines table to hold Items that a customer is ordering
- Create a Purchase Order Table to buy Inventory
- Create Purchase Orders Lines Table to include Items to be purchased
Even if you aren't costing, you may need to address how you are shipping and receiving your inventory. Which would mean more tables.
If you are still at this... Then create a routine that Queries the InventoryTransaction table and updates the ItemWarehouse table with the current OnHandQuantity for an Item. Doing this could be considered going against
http://bytes.com/topic/access/insigh...ble-structures, but this would be one place where your database might not be considered fully normalized (depending who you are talking to) since the query to look through a decade of Inventory Transactions can take quite a while. Then as you add code to Ship or Receive Inventory (or internal moves of inventory from one warehouse to another), have it create an InventoryTransaction then call the routine that updates the OnHandQuantity so that when you query an ItemWarehouse, the quantity available will be accurate.
There is a lot to this, but that is the basics.
2
