Can you create a table from a form?

[vincesavoldi]

Hi All,

I am having an interesting time on a project. I am trying to create a small inventory management database with BOM (bill of materials) control.

Today I am working on how to create the BOM's. In case your not familiar with inventory management, a BOM is basically a list of items and quantities used when creating a product.

Since it is possible for a single BOM to have over 200 Items associated with it, I can not have a single table with all my BOM's (fields max at 255)
So I was thinking I would be able to have a Master table with the BOM list and then create individual tables for each specific BOM.

I am not 100% sure how to make this all work together but I am struggling forward on my quest..

I was hoping I could create a form for BOM management, (create/edit/delete) but since I can not keep the BOMS in a single table I would need this form to create the new table.

Is it possible for a form to create a table? or do I have to create the table first than have a specific form per table to do the editing? or perhaps a drop down selector to choose which BOM/Table I am working on.

or am I going down the wrong rabbit hole?

 

[pbaldy]

You're going down the wrong rabbit hole. You would have a related table where each item was a record (row), not a table where each item was a field (column). The concept is called normalization:

http://www.r937.com/Relational.html

 

[vincesavoldi]

You're going down the wrong rabbit hole. You would have a related table where each item was a record (row), not a table where each item was a field (column). The concept is called normalization:

I think I understand relational databases and normalization but I will go read your reference link just to see and better understand what it says.

Yep, in order for my BOM to have each item (some are as many as 250 items) and Qty Needed, I believe have to have each BOM as it's own table.

Basically I am building 25 different Gadgets which some have as many as 250 parts that make up that gadget. Seems like the only way I can store the part information needed for each gadget is to have the gadget as a table with each part listed on a row and really only need 2 columns itemID (relating to the inventory table) and qty needed. This would make 25 Tables, one for each gadget.

I already have an Inventory table which has all my items with all their details. (*ItemID, Description, Vendor, Supplier, Bin Location, lead time, etc...) I originally wanted to have the item record store the qty available but I found that that is not the correct way to do it so I have now created tables for receiving items and removing items.

Eventually what I want to get to is a few simple forms for Receiving items into inventory, then processing the BOM to take the items out of inventory.

As usual it sounds easy but is really a lot of work for a novice like myself.

Once I get that all working I will work on creating and receiving Purchase orders to get the items into stock.

ps. I looked into the northwind inventory database, It handles Inventory quite nicely but could not find any way to store and process out the BOM.


thx for your help and pointers, I am off to read the link...

pss... another thought...
Should I have a master BOM Table with all my items as records (since there are more items) and the Gadgets as fields? the is exactly the opposite way i think it should work but since I can't have so many fields it's the only way that would work.

 

[Galaxiom]

I think I understand relational databases and normalization

Apparently not.

Yep, in order for my BOM to have each item (some are as many as 250 items) and Qty Needed, I believe have to have each BOM as it's own table.

No. One table with a field that indicates which Gadget BOM the record belongs to.

Should I have a master BOM Table with all my items as records (since there are more items) and the Gadgets as fields? the is exactly the opposite way i think it should work but since I can't have so many fields it's the only way that would work.

No.

 

[pbaldy]

I've never done a full blown BOM db. See if this is more on target for you:

http://access.mvps.org/access/modules/mdl0027.htm

The normalized approach isn't 25 gadget tables, it's a table with a field for the gadget as well as the others you mention.

 

[vincesavoldi]

No. One table with a field that indicates which Gadget BOM the record belongs to.

hmmm now I am really confused, let me try to break it down..

One table... I like that idea..
If this table has all 25 of my gadget BOM's are they records? (rows) or fields (columns)?

If they are records then how can I list all 250 items + Qty's required using the fields - And actually this would not work because not all BOM's use the same subset of items so even if I could list 250 items plus a quantity fields it would not work and be very much like a junk pile.

Or perhaps transposed from that...
One Table, 250+ records of items with fields showing which BOM and Qty needed. Or would I have a field for each BOMID plus a field for Qty Needed?
Hmmm.. that may work but that's the thought you also said no too...

Let's try to get back on track...

I can totally see how having one table per BOM would work what I need is an easy way for the user to use a form to create a new BOM table for a specific gadget.

If that's not possible I will just have to make all the tables myself.

thx again for input, it keeps me thinking and if I'm thinking I'm learning

 

[vincesavoldi]

I've never done a full blown BOM db. See if this is more on target for you:

http://access.mvps.org/access/modules/mdl0027.htm

The normalized approach isn't 25 gadget tables, it's a table with a field for the gadget as well as the others you mention.

I will look into that, thanks! I understand that a single table is best, just getting it worked out seems to be stumping me.

 

[Galaxiom]

and be very much like a junk pile.

Tables often look like a junk pile to a human. Data about the same thing is often scattered across several junk piles. But to an RDMS it looks organised.

One Table, 250+ records of items with fields showing which BOM and Qty needed.

Now you are on to it.

BOMID, PartID, Qty

Put a Composite, No Duplicates index on BOMID and PartID to prevent any possibility of adding the same part more than once to a BOM.

 

[pbaldy]

I'm going to get out of the way.

 

[vincesavoldi]

BOMID, PartID, Qty

I think I can see how that will work, it changes everything in terms of how I do things but hopefully I continue to learn the proper way to use the tools.

thanks!