MRP database

[actionashley]

I am trying to create a material requirements program in access for my company. I have multi level bill of materials and have a bom table and a bom details table with the component material usages. I pull the top level demand from a customer orders table but I can only get a single level by doing this. i.e. I'm missing when a product takes a component that takes another component. I need the component demand from the top level query to be used as the new sub assembly demand for the next level component and so on and be displayed on the current data sheet. I tried a union query but still could not figure it out.
Any help would be appreciated.

Action

 

[jzwp22]

Welcome to Access World Forums!

An MRP system--that's a challenge!

I would think that nested subqueries would be one solution, but since the number of levels is variable, it may not work. So the other option is to create something in VBA that loops through until all components levels have been evaluated. The data could then be dumped to a temp table on which your report would be based.

Out of curiosity, what does your table structure look like?

 

[actionashley]

Thank you for the reply jzwp.
I was able to get the nested query working but I get a "query to complex" error message when I go out to 6 levels so your Loop statement idea is probably the way to go. Only problem is I am a beginner and don't really know where to start to do that. I have read the missing manual on access but the VBA section is fairly light. Can you suggest a good book that would help me learn how to do this, or give me some hints to get me started?

I don't know the best way to present my table structure but here goes.

Products table with id and product number field
BOM table with bomid, product number field(linked to above)
BOM details table with detailsid, bomid(linked to above), component number(linked to products table!products numberid) and usage quantity

The input (or product demand) comes from an order details table with product number and order amount.

So I calculate the number I want as
component demand = order amount*usage quantity

I tried attaching a pdf of my relationships but don't know if it worked.

Can you suggest an easier way to present my table structure and relationships?

Thanks again

aa

 

[jzwp22]

I started out with Access VBA Programming for Dummies by Alan Simpson and still refer to it. I also have Access 2002 Desktop Developer's Handbook by Paul Litwin, Ken Getz and Mike Gunderloy which has helped me out as well.

Looking at your table structure, I'm not sure I would have used the same approach, but then again I've never done an MRP type database.

Basically, I would consider everything a part (just various types). A part can be a raw material, any part you buy, or a part assembled from other parts. Even the final product would be a part. A very simplified structure might look like this:

tblParts
-pkPartID primary key, autonumber
-txtPartNumber
-txtPartName
other fields


tblBOM
-pkBOMID primary key, autonumber
-fkPPartID foreign key to tblParts (this represents the upper level or parent part)
-fkCPartID foreign key to tblParts (this represents the lower level or child part to the parent part above)
-longQty (quantity of fkCPartID for needed to build the parent part)
-fkUOMID foreign key to tblUOM

I suppose that you have done a lot more research on BOM's than I have. In some respects your tables look somewhat similar, but I'm not sure why you have the Bill of Materials table rather than just the BOMMaterialsDetails?

I would have to do more research. It appears that you need to determine the number of levels ahead of time in order to have the correct number of loops or you might just call a procedure each time a part is found that has child parts tied to it. I will definitely have to think about it more.

 

[jzwp22]

I was doing some more research on this and found this site. It basically concludes that you have to compromise somewhere along the line. They do not quite answer my main question as to how do you know when you have reached the end of a BOM in terms of number of levels.

From the sound of it, nested queries should work as long as you know the maximum amount of levels you would encounter. I just wonder whether something in your syntax was wrong that resulted in the error you received. I think a VBA approach would also have the same hurdle with using nested loops.

Sorry that I could not be of more help.

 

[jzwp22]

I did find this site that has an example database illustrating the Celko BOM approach mentioned in the link in my earlier post.

I also found this site that might be of some help

 

[actionashley]

Thank you for the information in the above sites, glad to know someone has tried to do this already, albeit in 2005. In the Celko approach, if I read it right , he created enough columns to cover the maximum number of levels he might need. I got around that by creating a junction table, similar to a junction table for customer orders, so I could have an indeterminate number of levels. Maybe this makes everything more difficult but to do it right I think this is the way. Everything is working very nicely now for our products that have only 6 levels or less but the more complex products are an issue. The most versatile solution is to wright the VBA loop code as you earlier suggested. I am going to keep studying this problem and get the dummies book on VBA and hope I can hack through the VBA code. I am learning a great deal constructing this database, I feel it has many challenging problems to overcome which will help me with future projects. I welcome any more thoughts or advice.

aa

 

[jzwp22]

I was thinking more about your application more today, but could not come up with any earth shattering approaches. It sounds like you have made some good progress. Would you be willing to share a copy of what you have done (with any sensitive data removed, of course)? Perhaps others on the forum would benefit.

 

[actionashley]

The database is split with the back end being on the server and each employee (all 4 of us) having their own front end. Might be a little messy sharing. I will gladly share anything I can easily share however.

aa

 

[phillipmai]

I am also a beginner with access and want to create an MRP system.

BOM is where i'm stuck, every book i've read so far has nothing on this.

Any sharing would be appreciated.

 

[ByteMyzer]

Having participated in various MRP system programming projects, I salute you for your courage, for I can tell you that you have undertaken no lightweight project.

An MRP database application in Microsoft Access is not a project considered within the scope of a beginner MS Access user. You must first be conversant with the concepts behind the design of an MRP system (Inventory Management, Production Planning, Production Execution, Bills of Material, Work Routings, etc.) You must also be conversant, not only with table/query/form/report design, but also with Visual Basic for Applications as well, since many of the required activities (Hierarchical Multilevel and Summarized BOM Reports, Cost and Lead-Time/Offset Rollup Calculations, Order Scheduling and Routing, Inventory Allocation) can not be done with simple queries.

My first recommendation is to look for an existing MRP system/software package that can be adapted to fit your business' needs. You will get a far better and quicker ROI than you will by spending time re-designing the wheel.

If, however, you still wish to pursue this project, I recommend searching Google with "MRP design", for a plethera of research material. From this you can decide how best to scale your solution based on the relevant requirements for your business.

I then recommend searching Google with "Microsoft Access Visual Basic" for references to manuals, tutorials and examples to help you learn how to write Visual Basic code in MS Access. You can also search this site for excellent advice and examples on Visual Basic programming for MS Access. With this information, and with the information on the MRP design concepts, you can then decide how best to write the code for your particular solution.

If, after all of this, you make progress on your project, but have questions on specific issues with the development, feel free to post them here, and we will answer your questions to the best of our ability.

 

[actionashley]

Thanks for your input ByteMyzer. This is indeed an ambitious project. I would love to just purchase an MRP system but money is tight and we have to use what we have. I have an extensive planning background and have worked with several MRP systems at previous jobs so I do understand the complexity of such a system. This started out as just a way to track orders than I added shipping details and shop orders and inspection forms etc. It has grown to be a useful tool and I am slowly expanding it. The first big hurdle was how to explode a multi level BOM which I was able to create a work around with a union query but it is not as versatile as I would like. I do need to learn more about VBA and SQL to make my system work the way I want it too. I am reading access 2007 VBA for dummies and if I need more I plan on reading the book Bob L suggested. I am sure I will be posting specific questions as I start creating code to solve my issues. Again thanks for your input and any other comments or suggestions are welcome.

aa

 

[Colt45]

Hi,
I'm new to these forums, and to VBA, but I also work in an MRP-related field and have dealt with some of the issues you mention. I don't yet know enough VBA to try and programme the algorithm, but the approach I have taken for multi-level BOM explosion is to start from the absolute top or bottom of the BOM and run sequential queries to identify all the intermediate level. So if QueryA gives all the components of the top level, then query B will find the components of Query A. Query C will find the components of query B etc. Continue that until no more BOM levels are found (i.e. Query X contains no records), then add a few extra query levels to ensure you know if more levels get added.

I tend to append the query results to a table which builds up level by level as I've found that subsequent performance is much faster from a table than by constantly requerying.

This brute force approach has been quite effective for me, and thought I would share it.

 

[actionashley]

Thanks for your response Mr.45. Cool name.

I think we are taking similar approaches however your idea to use an append query to a temporary table is a good one. My objective is to get a material requirement report based on the open orders from the top level product (finished goods). Currently I am using multiple queries all stacked up in one big union query. It does have a finite number of levels it will go, which is ok for now but i would like to make it more versital as you suggest with some sort of if statement.

Are you running your queries from VBA, using SQL? I think my union query uses a lot of system resource and writing it in vba might help that. I get can't open another database errors sometimes.

I am reading VBA for dummies and hope to write something that will be more versatile and more efficient.

Thanks again for your input and please share anything else you think of.

aa

 

[Colt45]

Hi Ashley,
unfortunately I am not yet in any way proficient in VBA - but if I could write, the algorithm would work like this:
1. Start with list of top-level finished goods (in my environment they are independently identifiable, so no need to query the BOM to identify them).
2. Use the BOM table to identify the first (next) level subordinates and the draw quantity from the FG at that level
3. Append the level's information to the multi-level BOM table
4. Repeat 2 until no more subordinates are found (a null recordset being returned would identify that)
5. Another query will aggregate up the demand for each component as a given component could be a component of many FG.

Hope this helps!

 

[rockysmolin]

Ashley:

Make or buy? Here's an MRP system all done in Access. google E-Z_MRP
Rocky

 

[actionashley]

so old I forgot the question was still here.
Make a product so it has a bill of material, or buy so it has a vendor.

Thanks Rock I will check that e z mrp out.

i have made a pretty sophisticated mrp now ( 1 year later) but am refining continuously.

Thanks for the response

 

[InstructionWhich7142]

I have found update queries against a temp table to be the quickest solution to explode BOMs (I need to split them down against date requirements as well)

However I'm still bumping into major performance issues trying to do rolling subtotals after expanding these BOMs, may I ask what solution you used to get your MRP to calculate demands?

 

[actionashley]

Hi Chris,
To b honest I can barely remember what I did. I do remember I used nested quireis in order to calculate out all the levels and that I could only go so far (4 I think). I was going to eventually try to code it but haven't gotten around to it yet. That is way over my ability and would require quite a bit of learning and work I don't have time for right now. I don't notice any performance issues but maybe it would seem slow to you. Mine spits out a report with part number, quantity required, date required, and parent part driving the demand. Thanks for your input : )