Junction table inventory (1 Viewer)

Hek

Registered User.
Local time
Today, 12:31
Joined
Oct 30, 2018
Messages
73
Hi all,

I'm currently creating an inventory system database for my company. the company i work for is a manufacturing company that builds kits for cars. I've currently created a very basic inventory system which works well i.e when a kit is processed it automatically adjusts the stock levels. but now i need to add in that when a kit is processed it not only adjusts the stock levels for the kit but it also adjusts the stock levels for all the individual parts that go into making a kit.

I knew the way i had to go about this was to create a junction table which i did and off that built a form with a sub form in it to input the appropriate data. now that all the data has been input I'm a bit uncertain how to tackle my problem.

So ultimately what i need help with is how do i use the data that i have input to record all the parts when processed.

Attached are how i created the junction table and the forms for reference.

Any help or resources that i could read up on would greatly appreciated.

Cheers,

Hek
 

Attachments

  • Kits Form.PNG
    Kits Form.PNG
    68.5 KB · Views: 189
  • Junction table.PNG
    Junction table.PNG
    15.5 KB · Views: 203

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:31
Joined
May 7, 2009
Messages
19,169
[Supporting Part ID] of table [Supporting Parts] shouldn't be Autonumber, it must hold the same info as in [Supporting Part ID] of table [Kit Supporting].
 

Hek

Registered User.
Local time
Today, 12:31
Joined
Oct 30, 2018
Messages
73
[Supporting Part ID] of table [Supporting Parts] shouldn't be Autonumber

It is, but what would that change/help with?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:31
Joined
May 7, 2009
Messages
19,169
if not autonumber, it shouldn't be a Primary Key unless it will only contain 1 supporting part for 1 kit supporting.

it will help in identifying which supporting parts belong to a 1 kit supporting.
you also need another field in supporting parts table, qty required to make 1 kit supporting.
you can then multiply the quantity from this field to the qty in kit supporting.
this will give you the current inventory.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:31
Joined
Feb 28, 2001
Messages
27,000
You have obviously thought about this, but I'll comment on the issue of tracking both kits and their components. What's on the shelf?

That is a rhetorical question of sorts. Your business flow, and more specifically its timing, will govern what you do with the information you have.

When is a kit "made" and what happens to it between the time it is made and the time it is sold? That question perhaps govern what you do with the component info. It is your business and your vision, so you have to decide when things will happen and how they will happen. I can only guess the actual parts flow. Therefore this next comment is based on inference which is in turn based on the way you described your problem.

If I were doing this, I would have two sets of transactions. #1 would be the "kit made" transaction that would add one kit to inventory and remove multiple component parts at the same time. Transaction #2 would be the separate "kit sold" action which has no effect whatsoever on component levels. This is a "divide and conquer" approach where you break down actions to the point that they are more easily handled.

Obviously if I mis-guessed your work flow, that wouldn't apply - but the principle of "how should I handle having this new information?" is still illustrated.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:31
Joined
Feb 19, 2002
Messages
42,973
Kits are always a problem and I handle them the way Doc does. Kits get added into inventory and their component parts get taken out of inventory. You also need a transaction to break up a kit and put the parts back into inventory.

If your workflow is such that you make kits on the fly, that becomes more complex. I would modularize the process so it can be called from multiple places if necessary.
 

Hek

Registered User.
Local time
Today, 12:31
Joined
Oct 30, 2018
Messages
73
Thanks The_Doc_Man and Pat Hartman, that's what i was looking for. It gives me a good starting point for tackling my problem. Thanks again.
 

Users who are viewing this thread

Top Bottom