Item database with BOM lists

[nielsjmp]

Hi, Im totally new with access, please bear with me if its a bit weird or not clear what Im asking. Syntax is new

Im trying to build a database with documentation for some machinery I have been constructing. The total counting is around 110 special designed parts (with documentation) and around 1100 parts including Orings, screws, washers etc. There will be one db with drawings/documentation (pdf) and one with items both linked up on each other. However the one with items is a bit complex since a lot of the items are assemblies and need a bom-list. These lists can containb just one single item or it can be any number (typically btw 10-30 lines). Is it possible to make a "subrecord" to a record without making new databases? The subrecord will only contain information as "POS-NO ITEM-ID QTY" where the ITEM-ID will always refer to an item already existing in the database.

What Im imagining/hoping for is something a bit like this where the items 100014 and 100015 have subrecords/bom's attached (ISBOM=Y)...:

ID ITEM-ID ISBOM APPROVED APP DATE PRICE SUPPLIER
01 100010 N Y 2019-11-17 1.000,00 AA
02 100011 N Y 2019-11-17 1.100,00 AB
03 100012 N Y 2019-11-17 1.200,00 AA
04 100013 N Y 2019-11-17 1.300,00 AA
05 100014 Y Y 2019-11-17 5.100,00 WORKSHOP
_ POS QTY ITEM-ID
_ 001 002 100010
_ 002 001 100011
_ 003 001 100012
_ 004 001 100015
06 100015 Y Y 2019-11-17 800,00 WORKSHOP
_ POS QTY ITEM-ID
_ 001 003 100016
_ 002 001 100017
07 100016 N Y 2019-11-17 250,00 AC
08 100017 N Y 2019-11-17 50,00 AB

Thanks for any advice on getting started
Best - Niels

 

[vba_php]

I remember ur introductory thread when you told u would be creating this.

Is it possible to make a "subrecord" to a record without making new databases?

Of course. this is the purpose of a database. what you call a "subrecord" is actually called child data stored in another table and is related to your items. I assume when you say "bom-list", that stands for "bill of materials list"?? How about this:


table 1 (item basic info)
=========================
ID (primary key, autonumber), ITEM-ID, ISBOM, APPROVED, APP DATE, PRICE SUPPLIER


table 2
=========================
ID (foreign key, long integer), POS, QTY, ITEM-ID (you don't even need this field, as "ID" is what connects it to table 1)

 

[nielsjmp]

Hi, thanks for the suggestion. Yes, bom is bill of materials

What I wanted was if I could keep it inside the same item-table to avoid a lot of bom-tables consisting of just those few lines.

Already now I think due to around 20 parameters that can be configured in a lot of different ways that there are maybe 2-300 bom's available. On the longer run there will be maybe 1000-2000 BOMs in the database.

Im afraid that the number of tables will eliminate efficiency of the database radically - or maybe the number of BOM-tables will not be an issue
for speed/reading the data etc?

I also thought about just making a field as part of the item record with a long txt string containing the needed bom-data. Then its all in one table but a bit more messy to read

Thanks again - Niels

 

[vba_php]

Im afraid that the number of tables will eliminate efficiency of the database radically - or maybe the number of BOM-tables will not be an issue
for speed/reading the data etc?

in my recommendation, you will only have 2 tables and that setup is how a DB should be constructed anyway, so losing "efficiency" would not be an issue.

I also thought about just making a field as part of the item record with a long txt string containing the needed bom-data. Then its all in one table but a bit more messy to read

What you'll find if you do this is that you'll have to write functions and prolly some code when you try to parse that long string of data to display different parts of in form fields. that's why BOM data goes into it's own table.

 

[nielsjmp]

OK, thanks again. I get he point now. Its only one table with all BOMS in one with links from each BOM line to their respective parent. Perfect!

By including the ITEM-ID in the bom-table I can easily make a "where used" search as well from the same table, so this is a big help. One more structural problem solved!

Thanks! Highly appreciated

(Now the only problem is to get it done and make it work...))

Best - Niels

 

[jdraw]

Niels,

Read through all of this post that deals with Parts and Assemblies. It is a less complex set up than your proposed database, but has discussion of facts and concepts relevant to you. Getting your tables and relationships designed to meet your needs is important with any database. Testing your structures before getting too deeply involved with physical database is a proven strategy.

BOM processing is not a trivial subject.

You may find this info helpful with concepts/hierarchy.

Good luck with your project.

 

[kentgorrell]

BOM is a classic example of where you should use a recursive relationship NOT a Child table. The Items that make up an item are themselves items with the same (or very similar) list of attributes so they should be in the same table.

In its most simple form, where an item may only be part of one BOM then a simple Parent ID column in the Item table that identifies its parent is fine.
However where items may make up the parts of many different BOM (parent) items then a joining table with a ParentItem_ID, ChildItem_ID and Child_Quantity may be more appropriate.
WIth 2 FKs both referencing the PK of the Item table: tblBOM.ParentItem_ID = tblItem.Item_ID and tblBOM.ChildItem_ID = tblItem.Item_ID