Inventory/Stock Control (1 Viewer)

Bindy68

New member
Local time
Today, 13:51
Joined
May 6, 2019
Messages
4
Hi

I consider myself a reasonable Access user - but am certainly not a programmer.

I'm trying to write an inventory/stock control database (or modify an existing one) - I wrote one a few years ago but it was v clunky so want to improve this time around!

The system needs to record component parts, which can be assembled into products, which can then be assembled into kits. Goods inwards will usually be components, but goods outwards could be either components, products or kits. I don't need any kind of 'sales' interface as the goods outwards all go to a single place.

I also need to maintain stock levels/reorder levels etc, whilst allowing components to be provisionally allocated to jobs, only taking them out of stock once they are officially work in progress.

I've had a look at the MS Access Inventory template but am not sure its going be something I can amend to do what I want (it looks complicated?) or whether I am better to just start again.

Help! Any advice/guidance appreciated?!

Lynda
 

June7

AWF VIP
Local time
Today, 05:51
Joined
Mar 9, 2014
Messages
5,425
IMHO, manufacturing/assembly database is more difficult type to build. Topic of numerous discussions, some even have example db.

Then throwing inventory/stock control into the mix really gets complicated.
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:51
Joined
Sep 12, 2006
Messages
15,614
it is very tricky. You can have a table that manages a "part explosion" - so you can sell a "bathroom set" that consists of a bath, a panel, a toilet base, a cistern and so on. or you can pick the items separately. You can start running into complications when you have stock-outs of particular items, and need to put those items on back-order, and so on, or want to deliver a partial delivery, and deliver the balance when it's available.

It's even harder if you want to build in pricing/valuation of the stock.

I would have a serious think about your exact requirements before you start on this.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:51
Joined
Oct 29, 2018
Messages
21,358
Hi Lynda. Welcome to the forum! You'll likely hear the same sentiment from most of us that tackling an inventory/stock control database is not for the faint of heart. These are by no means a way to discourage you; but rather, just some words of caution, so you don't fall into a bad situation. Good luck!
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:51
Joined
Jan 23, 2006
Messages
15,364
Hi Lynda,

I agree with the other responders--Inventory is not trivial and can get very complex.
Here is a youtube video on Stock Control that may offer you some insight to your situation.

Good luck with your project.
 

Solo712

Registered User.
Local time
Today, 09:51
Joined
Oct 19, 2012
Messages
828
The system needs to record component parts, which can be assembled into products, which can then be assembled into kits. Goods inwards will usually be components, but goods outwards could be either components, products or kits. I don't need any kind of 'sales' interface as the goods outwards all go to a single place.

Hi Lynda,
just out of curiosity: in order to maintain parts and components stock at reasonable levels you need production targets and a detailed manufacturing map (as you are dealing with a multi-level supply chain). It would be interesting to know how your company makes stocking decisions presently. What is the scale of the operation? ( number of parts, suppliers, number and quantity of products) Do you have some sort of Excel spreadsheets to help you make the re-stocking decisions?

Best,
Jiri
 
Last edited:

Mark_

Longboard on the internet
Local time
Today, 06:51
Joined
Sep 12, 2017
Messages
2,111
Hi Lynda,

Some questions that you will need to work out the answer for before you start any serious design work;
1) If you have a nut and a bolt, but you can also sell them as "Nut & Bolt", are you counting them as the individual pieces only OR do you have some as "Individual items" and some as "Nut and Bolt set"?
2) Do you get into serialized part management? I.e. when you put one nut and one bolt together and put them in a package, does that package have a unique serial number?
3) Do you need to be able to unpack a "Nut and Bolt" (reducing your supply of this product by 1) and have their components go back into inventory (Nut supply increased by 1, bolt supply increased by 1)
4) Do you feel you would be comfortable with ONE table that holds all inventory, but using a second table to indicate when one item of inventory is actually a component of another (or others)?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:51
Joined
Feb 28, 2001
Messages
27,003
Welcome to the forum (since we see this is your first post).

While it is not always immediately obvious, what people are usually doing when they build something like this is they are building a data-flow model of their business. You appear to have a three-level stock model, with (1) components, (2) products, and (3) kits. This means you will have to know some things about how each of those is stored.

For one example, do you actually have kits already gathered up and packaged with all the products and components inside a single box - OR do you wait until you get a kit order to fill the box? The same question would apply to products. I.e. what is physically on the shelves when nobody is around? I ask this because "last minute assembly" ordering is not uncommon in many types of business.

Questions you will have to ask yourself are myriad and rarely simple. Let's start with "What do you want to get out of the system?" (If you think that is a simple question, guess again.) If you are still evaluating, here are a couple of rules I use:

Old programmer's rules #1: If you can't do it on paper, you can't do it in Access.

That is, build a diagram of data flow and product flow as a pictorial representation of your business model. Understand what processing needs to be done for things like "job allocation of product" or "component re-order from vendor" or any of the gobs of things a business does to keep product on the shelves for sales or delivery or whatever you do with these things. Make a roadmap so that you can see where the project needs to go. In this process, you will identify various stages of data from manual or automated input as vendors deliver parts through consumption of parts for creation of products or kits to delivery of goods to detecting that your are low on left-handed veeblefetzers so you can order them in time for the next product that needs them.

Old programmer's rule #2: Access won't tell you anything you didn't tell it first.

That is, Access really does only one thing well. It makes tables, queries, forms, reports, macros, and modules at your direction. You have to provide the subject-matter knowledge. Access knows doodlum-squat about inventory - but YOU know about it. This means that if you want to see something specific in the output, you need to assure that the specific something was input or computed somewhere. Yes, this means working BACKWARDS through your road map to assure that every output has a valid source, whether via input or formula or both.

This probably sounds hard. If you think so, then GOOD. You were paying attention. But, you ask, what is the payoff? Mathematician Nicklaus Wirth, the "father" of the PASCAL programming language, is often quoted as saying that 80% of all program problems originate in bad data design. I don't recall the exact words and they might have been in Polish anyway. So the up-front phase is the biggest, nastiest, and most important phase of any large project. If you walk in expecting this difficulty, you won't be disappointed now - OR later when everything has been anticipated and is coming together nicely. 'cause that's the payoff of sweating blood in the design phase.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:51
Joined
Sep 12, 2006
Messages
15,614
@TheDocMan
This probably sounds hard. If you think so, then GOOD. You were paying attention. But, you ask, what is the payoff? Mathematician Nicklaus Wirth, the "father" of the PASCAL programming language, is often quoted as saying that 80% of all program problems originate in bad data design.

And this goes the other way. Get the design right, and development becomes much more harmonious, and you make progress more quickly, without having to go back and re-engineer.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:51
Joined
Feb 28, 2001
Messages
27,003
True that, Dave! When you have good data setup AND know what you need to do, actually doing it is almost (not quite) a mindless exercise.
 

Users who are viewing this thread

Top Bottom