Starting a inventory database for a job shop (1 Viewer)

MillerTime

New member
Local time
Yesterday, 22:07
Joined
Dec 9, 2020
Messages
5
I have been tasked with creating a inventory database for my company, we are small job shop that produces specialized trailers for moving shipping containers. Right now we are producing one at a time but want to start upping production to producing a few a week, hence the need for a database. I've seen a lot of great templates for inventory but they all seem tailored to retail where the product is bought, stocked, then sold. In our case we only sell six products but they are made up of kits, other stand alone parts, and possibly one off metal products we might have to manufacture in shop to fit the need of the trailer.

Our trailers are assembled with what we call "kits", kits will be sets of parts we order from other manufactures such as our electronics or hydraulics . These kits are then put on the main frame to make the final assembly. Some kits like the electronics kit are order well ahead of time and kept in stock, but on the other hand things like the hydraulic hoses and the kit that contains bolt-on metal pieces are order one at time so they arrive just before the build.

My boss wants to track everything from the nuts and bolts up to where and what customer the trailer is sold to. I've only made a very small example database before so I'm a bit confused / intimidated on how I want to structure the overall database. I want to be able to know stock levels of all parts along with how many kits we have available. Basically I'm not sure how to map this multilayer production on to a database.
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 22:07
Joined
Jan 23, 2006
Messages
13,642
Inventory/stock control is not a trivial subject. There are examples on the forum that may help you with design and context.
I assisted a member a few years ago and provided a draft model for his trailer build application, but the details are likely quite different from yours. Still t may give you some ideas. There are several articles on database planning and design and more in the link in my signature. The tutorials from RogersAccessLibrary in that link are a great source for database design and relationship help. You have to work through the tutorials, but you will learn a procedure you can use with any database.
Good luck. Don't skimp on the analysis.
 

Minty

AWF VIP
Local time
Today, 03:07
Joined
Jul 26, 2013
Messages
8,198
Without wishing to put you off as @jdraw has said, this is not really a task for a beginner.
I recently added inventory stock control to an existing system, similar requirements to you, stock parts used to make assemblies and stocking requirements for batch production stock planning.

I would consider myself relatively experienced and I would estimate it took me 3-4 weeks of work to get it useable.
Bearing in mind they had an existing system with the assemblies and stock parts required already set up, as well as all the forms to handle that.
There were additional complications with assembly revisions, and stock part revisions, as well as storing and maintaining multiple locations for stock items.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:07
Joined
Feb 28, 2001
Messages
19,415
This is your first post so we don't have any feel for your abilities. Therefore if I offer advice that shoots too low, please forgive me. To start such a project, you need to ground yourself thoroughly in concepts of normalization. You need to do some reading.

First, look up "Normalization" in this forum (SEARCH is at upper right) or using "Database Normalization" on the general web. If using the general web, start with articles from .EDU domains because the .COM sites often have something to sell that is proprietary rather than general in nature. But they can still have lots of good advice.

Second, that bit about "kits" suggests to me that you will need to read up on "Junction Tables."

Third, I need to give you some advice for the design phase of your project. I call these "the Old Programmer" rules.

1. If you can't do it on paper, you can't do it in Access.

This is a "readiness" rule rather than an "impossibility" rule. Analyze your work flow and your work requirements thoroughly so that you can draw a step-by-step data flow diagram/recipe of where your data originates, where it is used, where it will be stored, etc. Think of it as a roadmap. If you are not sure where you are going, how will you be sure that you have arrived? Further, this document would become part of the project notes and history for future maintenance, looking ahead to the day when everything was working fine, then something went wrong and you look back at code you haven't touched in months. And you ask yourself, "What the heck was I thinking here?" When you have a design document that makes sense, you can start coding.

2. Access won't tell you anything you didn't tell it first.

This is an "expectation/implementation" rule. MS Access is an expert at building tables and queries and forms and reports and macros and code modules. It knows ZERO POINT ZERO about your business. You will be the subject-matter expert. You will provide the information that Access will be able to spew back to you. In the design phase, you should include some element of expectation. After that, you need to assure that for every desired output you have the necessary inputs and formulas. If you want to see X, you need a source of X. If you want to see XYZ, you either need a single source of XYZ or you need sources for X, Y, and Z and the formula to combine them. This sometimes may include back-tracking through your process to see where each element needs to be included. It can be tedious, but it is usually worth it.

3. Never let the tail wag the dog.

This is an "implementation" guideline. Build your project to follow reality and requirements. Do not let the database's limitations become a limit for your business. If your DB and your business disagree, the DB is wrong. The DB should track what you do; it should rarely to never dictate what you do. Exceptions exist if your analysis discovers sloppy manual procedures that need cleaning up. In those exceptional cases, it was the analysis rather than the DB itself that would have dictated a change. Therefore, the tail still isn't wagging the dog.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:07
Joined
Oct 29, 2018
Messages
14,620
Hi. Welcome to AWF!

I have nothing else to add to all the advice already given. Since this is your first post, I just thought to welcome you. Cheers!
 

MillerTime

New member
Local time
Yesterday, 22:07
Joined
Dec 9, 2020
Messages
5
Amazing advice, thank you for the help. I took a access/Database class in college where we did a few small example and tutorials in access then moved into creating our own database through Microsoft MySQL server and a program we created ourselves in C#. So I get the concepts and how a database functions. I've just never done one on this scale and was unsure of a starting point. After doing some reading and actually starting the database I feel that I'm getting a bit of understanding and confidence with the thing.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:07
Joined
Feb 19, 2002
Messages
31,505
Hi and welcome. You've gotten some excellent advice so far but I'd like to talk about kits. Kits are the bane of inventory applications because they are assemblies that are actually built and stored on the shelves assembled rather than as individual components. From how you described them, they are simply parts. No different from a rivet, just more expensive. So just store them as a detail part. You don't care that they are assemblies. You don't care what parts they include. You are storing the kit rather than the individual parts. You are not building the kit, you are using it as a detail part. They might be included in another assembly on their way to building one of the six end items.

Your application requires a BOM (Bill of Material) and that's what makes it different from the retail inventory applications. You need to know both what you have as parts and how to assemble those parts into a finished product. So you might want to look for samples that include a BOM.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 03:07
Joined
Sep 12, 2006
Messages
14,419
Here's a link to an old thread. I provided a link in this thread to an old inventory database originally built for Access97/Access2003 database that works fine in later versions of Access. I call it the Dharamsala Tea database, because the first item you see is called that.

The only thing I change is to store sales (ie items moving out of inventory) as negative movements, as it makes evaluating current inventory far easier.

In my opinion it's the best user friendly inventory management database available, and it's a great starting tool. I've built on this for many projects.

The issue with sub assemblies and parts explosions, is that you have to decide how you break them out into individual items, to avoid double counting. It's a matter of taste really. Personally, I think if something has been "built" or "worked" into another part then it's no longer the original part. (I see Pat just mentioned this above). However, if you want to pick a selection of items as a "macro part" or "package" then it's probably better to keep that separate. You might sell the parts separately, and alternatively the same part might form part of multiple explosions. You also need to deal with the side issue of managing partial stock outs - so you can't fulfil a complete parts explosion, as some of the parts that are required for the full package are out of stock. Deciding how you deal with this is going to be a management issue. Do you allocate the partial requirement, while the out of stock stuff gets shipped, or do you leave it for other orders to use, and then risk a further supply problem with the original order when the back ordered stock arrives.

Note in passing that quantities is relatively easy to deal with in inventory. Once you bring in prices and costs, inventory is far far harder.

Anyway, see this thread.
(1) Best way to prevent multiple postings | Access World Forums (access-programmers.co.uk)
 

Users who are viewing this thread

Top Bottom