Stock Management in Access

[hellboy83]

Hi,

I have been asked to design a database in access for internal stock management in my department. As a first step I am doing the analysis, building the required tables and the respective relationships.

The link for the ERD diagram image it´s at the top in brackets with the title.

Client_tbl: internal employees from different departments requesting the material in the stock.

Product_tbl: products currently in the stock.

Supplier_tbl: our suppliers.

ProdPurchase_tbl: purchases from the supplier.

Employee_tbl: employee that distributes the requests from other departments.

Distribution_tbl: information about the delivered product.


I want to know if I have missed anything on my ERD diagram. If there is anything missing please let me know guys.

 

[KenHigg]

Just as a quick glance you'll need a transaction table for issues (or did I miss it?)

 

[gemma-the-husky]

how long have you got to do this? I would never recommend building a stock system from scratch. Its really hard to do - REALLY HARD

Try and find a free template- I know MS have one, but I am not sure where it is- otherwise consider buying something

Unless its very rudimentary, this is going to take much longer than you think


offhand, you may have to deal with any or all of the following

Receipts/Issues
Stock Count Adjustments
Stock Takes
Price/Quantity
Costing Methods
Stock Valuation
Partial Picking Success
- a) due to no stock
- b) due to Stock Expected but not found
BackOrders
Stock Locations
Multiple Stock Locations
Serial Numbers
Cut-Off
Authority Levels
Reports
Forms/Form Design
Parts Explosions
Serial Numbers
Suppliers
Supplier Orders
Alternate Suppliers
Minimum Stock Quantities for Reordering
Stock Aging
Obsolescence
Deterioration

probably loads more.

Even something as simplistic as searching for products is tricky - you either redefine your selection based on partial product numbers, but this means that you need to know the leftmost characters in a product number.

If you have to let users enter search phrases to search for products - thats harder, and slower for big populations.

See if you can map this out on a series of spreadsheets first
Have one tabbed sheet per product and see how you get on. You will get a feel for how hard it is that way

 

[tmd_63]

This can take a long time. I know as I developed an almost complete MRPII database from scratch and it took me over nine months, working every evening in my own time to complete it.
It covered everything from the customer orders to the stock status, to ordering parts for build, to assigning batches. The only thing it didn't do was to schedule time for department workloading.
It was a full split system so that the design department could manage the BOM's, the purchasing department could order new stock, the sales department could place customer orders and the manufacturing department could schedule the builds. Each had their own login and permissions and each had different access rights from their own PC's to a central data-store. All done in Access 97.

 

[neileg]

Your ERD is a long way from being workable. Do a search in these forums for inventory or stock control, there's plenty of material.

And the other posters are correct, this is definitely a non-trivial application. It's a shame that so many novices choose such a tricky application to cut their Access teeth on!