Rocky Smolin
rockysmolin at bchacc.com
Thu May 22 08:03:20 CDT 2014
Here's a sample report: http://www.e-z-mrp.com/wp-content/uploads/2014/02/BOM-603.jpg R -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin Sent: Thursday, May 22, 2014 5:39 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Finding the levels in a bill of material I wrote a commercial bill of materials processor, and, while it has an internal level number field, it's for use only in the cost roll up function and is not displayed to the user. The actual level of a part in a multi-level assembly only needs to be known in a report of the specific assembly. So it is displayed in my reports as both a level (not the one in the product structure table) and as an indent on the report. To make the reports I create a temp table with all the report data using a 'push/pop' scheme where a component that has a BOM causes a push onto a stack, then next level below is added to the temp table, and at the end of that sub-assembly's BOM (assuming no parts on that level with BOMs) the stack is 'popped' and the level above continues processing. HTH Rocky -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of ewaldt at gdls.com Sent: Thursday, May 22, 2014 4:46 AM To: accessd at databaseadvisors.com Subject: [AccessD] Finding the levels in a bill of material In a simplified bill of material (BOM), let's say I have (first line contains field names): PartNumber, Parent , Level 001, (null), 0 002, 001, 1 003, 001, 1 004, 003, 2 005, 001, 1 006, 005, 2 007, 005, 2 008, 007, 3 002, 007, 3 009, 005, 2 010, 001, 1 I put the levels in to show what I actually need to find. In other words, I have the part number and the parent's part number, but don't have the level. so pretend the levels are all blank. In the real tables, there could be over 20 levels and 20,000 records. Since it's zero-based, the first is level 0, of course. Any parts showing the first one as the parent logically will be level 1. Those are the easy ones. Part numbers can appear more than once and at different levels (not as a descendent of themselves, of course). Note that part number 002 shows up as a child of 001, and also as a child of 007 (with all that messing around, you have to figure Bond has some kids somewhere). BTW, each also has an ID number (lngID), assigned in the order the parts are presented. Does anyone have a good algorithm to find the Level for these? Thanks in advance for any and all help. Tom Ewald Mass Properties General Dynamics Land Systems -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com