I recently realized my hobby has turned into a business and I need some kind of jewelry inventory system, I know you make findings but do you have any experience building jewelry inventory systems ?
I would use Microsoft access to build my system.
The first thing to do in setting up a computerized inventory system is to establish a simple data base of 2 maybe 3 tables. The first table would be your master table where each item is assigned a product code number and it would resemble the following. By setting up a master table you will eliminate the need for keeping different tables for different types of inventory. It also sets up your system from the beginning for maximum flexibility which is essential for a business to prosper for anything but the shortest term.
Also, not knowing how much disk space etc you have I might add a 4th field to this table for a picture ( thumbnail) of the part
|
required |
required |
required |
optional |
|
Part Number |
Part Description |
Inventory Type |
Photo |
| 1 | Blue bead 15 mm round | Component | |
| 2 | filigree base | Component |
|
| 3 | Blue bead necklace | Finished product | |
| 4 | Amber bead 7 mm | Component | |
| 5 | 4 X 6 Poly bag | Packaging | |
| 6 | .095 wire | Raw Material | |
| 7 | Blue bead bracelet | Finished product | |
| 8 | Filigree Brooch | Finished Product | |
| 9 | Blue bead 7 mm no hole | Component | |
| 10 | Brass flower finding | Component |
|
The next step would be to set up the actual inventory table using a structure like the following
|
Part Number |
Quantity on hand |
Unit
of |
Date of last transaction |
Set up date |
| 5 | 1000 |
pieces |
04/04/2004 | 02/01/2003 |
| 10 | 144 |
pieces |
06/25/2004 | 06/25/2004 |
| 1 | 63 | pieces | 05/01/2004 | 01/15/2001 |
| 8 | 3 |
pieces |
06/01/2004 | 07/01/1959 |
| 6 | 3.5 | pounds | 07/06/2004 | 07/06/2002 |
The third table I would build from the beginning although it has nothing to do with inventory control (it will just make life easier if its done early) would be a vendor table and it would look something like this.
|
Part Number |
Vendor |
Vendor part number |
| 1 | Bead O'Rama | BB15x42-sd |
| 2 | |
6452-1R |
| 4 | Bargain Bead | Amber x234 |
| 10 | |
8997 |
As far as transaction recording and the various reports you are going to need, it is best to keep them simple like the data base itself. We hope this little tid bit helps and at least gives you something to work with as you continue building your inventory management system. Keep in mind also that a strong inventory control system can greatly enhance your build to order profits.