Bill of Material Module

[DToronto]

Dear fellows , I'm quite new in database development and got already a project where I just found that I have to swallow an elephant in order to get it done ....
Being an owner of small manufacturing unit I'm struggling to make an application by myself to mimic MRP/ERP systems or at least part of it using Ms Access.
Question that I have refers to:
How to built a Bill of Material module (later on how to view it /explosion parts/ , edit and use components to find their multiple parents-so called "parts implosion")
In standard BOM we have parents and components in multilevel fasion, assemblies and subassemblies and there are all interconnected by relationship rules.

Any developer had similar project ? I need just a guidelines how to start and what objects will be proper to use.


Thanks in advance

[NeoPa]

I hope someone may be able to point you in the right direction, but this seems altogether too large a question to deal with here, Even if only guidelines are required.
As I say, I hope you get something helpful.

[fishbait01]

Hi DToronto,

I have done many project which I had to create "Where used", "Explode a BOM", etc. I find that the simple database structure is to have 1 table for a Part Master List (tblPartMaster) and 1 table for the BOM (tblPartBOM). This would allow you to have unlimited levels to a BOM.

tblPartBOM table would have these basic columns (you can add more if needed):

Assembly Part Number (AssyPN),
Component Part Number (CompoPN),
Quantity per assembly (QPA).

AssyPN is the Make Part and the CompoPN is the 1st level components/sub assemblies used (at this point component and sub assemblies are the same, if a CompoPN appears in other records as AssyPN then you know this it is a sub assembly. Also, if you do a query SELECT DISTINCT AssyPN FROM tblPartBOM, this would return a list of all your Finish and Sub Assembly Parts).

You will have to use some kind of recursing code to read it. To manage it, I would extract the top level BOM of a Part into a temporary table allow changes and then write back. This is not necessary the best and could be tricky especially if you have multiple users.

For reporting a BOM Part, again I would use a temporary table were you would extract all relevant records including the different levels and then call a report using this temporary table as it recordsource.

I hope this helps.

[DToronto]

Hi Fishbite , I'm quite impressed. This is exactly what I'm looking for

Thank you for your time. I really appreciate your help

p.s. Any suggestion on 'recursing code' ?

[NeoPa]

Recursing code works well with hierarchical structures.
A procedure that processes a single generation can call itself for the next lower level.
You need to follow that pattern while writing recursive procedures.

[IanS]

If all you want is a BOM module then you may not need an aversized ERP/MRP solution. Take a look at miniMrp.

www.minimrp.com

Here's some ad copy from their website

miniMRP is a new, easy to use inventory manager with some additional features usually only found in larger ERP/MRP systems.

why maintain an overweight, over-complex ERP/MRP system when all you really want is something that will manage an unlimited number of inventory items and when required, build, manage and cost single and multilevel parts lists and Bill of Material of almost any number of levels or complexity

[IanS]

Oh, I forgot to say that the back-end database for miniMrp is a standard MS JET database file (same format as MS Access) so although miniMrp is not based on MS Access you can still us MS Access to add more forms and reports if you feel like expanding on what is already a real neat solution.