# Creating a Stock Management System



## goldenvision (Jan 4, 2008)

Has anyone had any experience in creating a Stock Management system or knows of any good starting points.

I have been tasked with creating a fairly large one for a work project and I'm looking for some pointers.  I've had a quick trawl through the web and there doesn't seem to be too many publications on the subject.


----------



## BrianB (Jan 10, 2008)

I had 5 years experience with stock control some years ago. The basic method is to keep a record of all stock movements in and out of storage in much the same way that a bank statement shows movements of money. Excel and Access are both well suited to this task. The first decision is which application to use the bearing in mind that versions of Excel before 2007 are limited to 65,5536 rows. If the required number is likely to exceed this I would personally use Excel as a front end connected to an Access table.

The basis of the system is a single simple table with field headings at the top and data underneath. Do not be tempted to use separate stock listings. This is what big companies use and is the ideal. If you can keep closely to this Excel is still tool for the job. If you need to refer to multiple suppliers of a product and/or multiple outlets then you may need to use Access with its ability to use separate tables and link them together to avoid repetition of data - rather than using Excel lookup tables. If Sales need to be taken into account I would use separate tables for that. You might need a separate table to keep track of orders from external suppliers too.

Here is a rough example ('code' used to format the data). You will need to start everything off with a manual stocktaking.

```
DATE    TRANSACTION_REF     ITEM_REF    DESCRIPTION   GOODS_IN    GOODS_OUT  ORDERS
1/1/08  stock check         A1          Item1            1000
1/1/08  stock check         A2          Item2             500
1/1/08  stock check         XX          etc               200
2/1/08  delivery 1234       A1          Item1             100
2/1/08  Order xxxx          A2          Item2                                 500
2/1/08  stores issue 999    A1          Item1                        500
2/1/08  stock check adj     A1          Item1                         10
```
The base table does not need to be much more complicated than this because updating the table and the hard work of pulling various items of data togetether eg. supplier name, price, etc. can be done with userforms and lookup tables (or not if required). There will be separate processes for calculating stock listings and showing details of individual stock items. Calculations from such tables are very fast and easy nowadays.

A way of helping to keep data accurate is to get stores to let you know when any item has none left when they remove anything.

Hope this helps.


----------

