Simple Stock database

[grogmi1]

Hi,

I am looking to create a simple database which has a list of parts and their stock levels and when I raise an order it takes the quantity ordered from the current stock level. I have looked at lots of templates but they're far too advanced for what I need. I simply want to be able to pick the parts on an order form, enter the quantity ordered and for the quantity ordered to be deducted from the Stock level. I'm an absolute novice with MS Access so any help, guidance to start me off would be very much appreciated

 

[Minty]

You should always try and calculate the stock level on hand, NOT store it.
There are numerous reasons for doing it this way, but the simple explanation is that is is much easier to keep track of things.

Initially you will want a list of parts with a part number, description, possible a cost, and a transaction table listing the part ID ,transaction type and a quantity (Stock Input or Output).
If you deal with parts from multiple suppliers that could share a common part number you will want your own unique PartID or a Primary key based on Part number and Supplier. The former is more robust.

 

[MarkK]

Some facts are by definition calculations based on other facts, like someone's age is always a calculation that uses the current date and a birth date. A bank balance is always deposits minus withdrawals. Similarly, the most fundamental fact about your stock level is this . . .

StockOnHand = StockIn - StockOut

So record your stock in and your stock out, and then calculate your balance of goods.

 

[jdraw]

Further to what Minty and Markk have advised,
see this article by Allen Browne

Also, I don't know that there is such a thing as a simple stock database. They have a way of morphing into something less simple.

I'm going to suggest you watch this 26 minute video on Stock Control Database to see some of the things involved and some discussion of inventory management. It will help you with scope and design.

Good luck with your project.

 

[grogmi1]

Thanks for the help and guidance guys. The article by Allen Browne has been quite a help, thanks jdraw