Inventory Recording (1 Viewer)

Hek

Registered User.
Local time
Today, 03:30
Joined
Oct 30, 2018
Messages
73
Hello all,

Currently a bit lost and any help would be greatly appreciated.

Background:The company i work for makes and sells kits which contain lots of individual parts. What I'm trying to achieve is an inventory system which records the number of kits sold and all of the individuals parts used in said kit.

What I'm having trouble warping my head around is how I'm meant to link the individual parts to the kits.

My current set up is:

tbl_kits;
Kit ID-Autonumber
Kit Number- Short text
Description- short text
Supporting Part ID- Number
Supporting Part Quantity Required- Number
Kit inventory level- number

tbl_Supporting Parts;
Supporting Part ID- Autonumber
Supporting Part- Short text
Description- Short text
Inventory Level- Number

Any guidance would go a long way.

-Hek
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:30
Joined
Aug 30, 2003
Messages
36,118
I think you need what's often called a junction table. It would have fields for kit id, part id and quantity (and typically its own autonumber key field). If a kit was made up of 10 parts, there would be 10 records in that table.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:30
Joined
Feb 28, 2001
Messages
26,996
To add to Paul's comments:

So you have a list of parts. Historically in this forum, there have been two approaches, but I think the most popular is to have individual part numbers for each component part and one more part number for the assembly. The difference between them would be that a component part does not appear in the junction table that lists components in an assembly, but the assembly DOES appear in that junction table with one or more records of the parts that are used in the assembly. You treat the assembly just like any other part.

The other approach I recall, which I believe most of the forum members disliked at the time, is you have a parts table and the assembly table, but you make a UNION table so that the assemblies and the components appear together for the UNION but are separate otherwise. The dislike arises from treating assemblies differently than individual parts even though you sell both and both can appear on an invoice.

A wrinkle that occurs in either case is this: You have a bunch of parts and a bunch of kits. Then you sell a bunch of kits. The parts come from a supplier so the transaction for adding stock is just "Supplier Delivery" or some such description. But what about having to assemble the parts together to make a new kit? Because in that case, you have to do a stock add to the assemblies table and a stock remove from the parts table. This is where your own business rules must guide you because the details depend on your business model.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:30
Joined
Feb 19, 2013
Messages
16,553
I'll also throw in that your field kit-inventory level sounds like a current stock quantity rather than a min or max level to be maintained (aka Kaban)

If the former, this should be calculated as and when from transactions, not stored as a value. Transactions also should be in one table - not separate ones for receipts/sales etc
 

Hek

Registered User.
Local time
Today, 03:30
Joined
Oct 30, 2018
Messages
73
Thanks everyone, all your contributions have been very helpful!
 

Dreamweaver

Well-known member
Local time
Today, 10:30
Joined
Nov 28, 2005
Messages
2,466
Tblparts
Tblproducts
TblAssemblies
Tblsubassemblies
A union query would end up so slow

The last production management system i built i treated the stock control like a double entry accounting system just highly automated.

I bed on tab but will see if I can pull a table structure out for you tomorrow should you still need it
 

Hek

Registered User.
Local time
Today, 03:30
Joined
Oct 30, 2018
Messages
73
I bed on tab but will see if I can pull a table structure out for you tomorrow should you still need it

If you could do that, that would be amazing.
 

Dreamweaver

Well-known member
Local time
Today, 10:30
Joined
Nov 28, 2005
Messages
2,466
One other thought it was not only the stock you needed to keep track of you also needed to track the subAsemblies that make up the end product being that without the parts and the subassemblies you cant build a complete product.
 

Users who are viewing this thread

Top Bottom