Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-18-2019, 01:43 AM   #1
nielsjmp
Newly Registered User
 
Join Date: Nov 2019
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
nielsjmp is on a distinguished road
Item database with BOM lists

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


Last edited by nielsjmp; 11-18-2019 at 01:55 AM.
nielsjmp is offline   Reply With Quote
Old 11-18-2019, 03:22 AM   #2
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 1,307
Thanks: 127
Thanked 313 Times in 300 Posts
vba_php is an unknown quantity at this point
Re: Item database with BOM lists

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


Quote:
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)
vba_php is offline   Reply With Quote
Old 11-18-2019, 03:58 AM   #3
nielsjmp
Newly Registered User
 
Join Date: Nov 2019
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
nielsjmp is on a distinguished road
Re: Item database with BOM lists

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

nielsjmp is offline   Reply With Quote
Old 11-18-2019, 04:05 AM   #4
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 1,307
Thanks: 127
Thanked 313 Times in 300 Posts
vba_php is an unknown quantity at this point
Re: Item database with BOM lists

Quote:
Originally Posted by nielsjmp View Post
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.



Quote:
Originally Posted by nielsjmp View Post
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.
vba_php is offline   Reply With Quote
The Following User Says Thank You to vba_php For This Useful Post:
nielsjmp (11-18-2019)
Old 11-18-2019, 04:44 AM   #5
nielsjmp
Newly Registered User
 
Join Date: Nov 2019
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
nielsjmp is on a distinguished road
Re: Item database with BOM lists

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
nielsjmp is offline   Reply With Quote
Old 11-18-2019, 05:43 AM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,398
Thanks: 107
Thanked 2,060 Times in 2,003 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Item database with BOM lists

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.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by jdraw; 11-18-2019 at 05:51 AM.
jdraw is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating dummy copy of database using sharepoint lists treeman General 2 08-09-2017 11:21 AM
Sharepoint lists vs Web Database Geirr Access Web 1 09-01-2015 11:40 AM
Creating lists of items and running a report on total sales of items in the lists... thesurfagents Reports 0 09-27-2012 11:44 AM
Managing multiple small lists in one database Mr. B Sample Databases 0 07-11-2009 05:20 PM
Copying data between lists with value lists as recordsource CrArC Modules & VBA 2 09-14-2007 12:30 AM




All times are GMT -8. The time now is 11:37 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World