Re: Recursive BOM

Thanks, I explored the nested sets solution, I found all the info
on-line including a sample mdb file but this won't address my needs as
in my case, the same component or semi-finished can be used in several
finished or semi-finished products....


lesperancer@xxxxxxxxxx wrote:
you either need to explode the bom into a temporary table from which to
report on
or you can change your structure to use 'nested sets' (lots of google
info)

pascal@xxxxxxxxxx wrote:
Hello All,

I'm a middle-low level user of MS Access 2000. I'm trying to find a
solution to what appears to be a common problem. By browsing the net
and the groups, I've seen several posts and solutions to that problem
but I've not been able to find a ready to use query or module directly
working into MS Access.

The problem is the standard BOM problem.
I have a BOM structure setup in 2 tables:

tblParts:
PartID PK Number
Part Code Text
Part Description Text

tblBOM:
Parent Code Text
Child Code Text
QTY Number

And of course we can have an unlimited number of level in the BOMs.

Here's a 3 levels exemple:

tblPart
PartID Part Code Part Description
1 Finish1 Finished Part 1
2 Finish2 Finished Part 2
3 SemiFinished1 Semi Finished Part 1
4 SemiFinished2 Semi Finished Part 2
5 Component1 Component 1
6 Component2 Component 2
7 Component3 Component 3
8 Component4 Component 4

tblBOM
Parent Code Child Code QTY
SemiFinished1 Component1 1
SemiFinished2 Component1 1
SemiFinished1 Component2 3
Finish1 Component3 2
SemiFinished2 Component3 3
Finish2 Component4 3
Finish1 SemiFinished1 1
Finish2 SemiFinished1 4
Finish2 SemiFinished2 1

I'm trying to have a query that would give me for a given part, the
complete list of components whatever the number of levels in the BOM.

For exemple
Finish1 Component3 2
Finish1 Component2 3
Finish1 Component1 1

I've created a database with the 2 tables available at:
http://www.zembra.net/BOM.mdb

Any help to develop/integrate this query would be more than greatly
appreciated.

Thanks, Pascal

.

Relevant Pages

  • Recursive BOM
    ... The problem is the standard BOM problem. ... tblBOM: ... Here's a 3 levels exemple: ... I'm trying to have a query that would give me for a given part, ...
    (comp.databases.ms-access)
  • Re: Recursive BOM
    ... The problem is the standard BOM problem. ... tblBOM: ... Here's a 3 levels exemple: ... I'm trying to have a query that would give me for a given part, ...
    (comp.databases.ms-access)
  • Re: Reprise: Celko Nested BOMS
    ... I loved the Nested Sets idea. ... find a way to extend it to most of BOM. ... The example in the database seem to assume that all parts are unique ... repeat of a part number (= memberName in the database) also adjusting ...
    (microsoft.public.access.queries)
  • Re: Deleting a table in the backend using VBA
    ... subcomponent of a BOM as it drills down into the BOM. ... set of data (append query). ... I am trying to delete a table in the backend database using code in the ... frontend, for querys used in the frontend afterwards. ...
    (microsoft.public.access.modulesdaovba)
  • Re: How do I create an Exploded Parts List in Access?
    ... If you know how deep the BOM is, you can create a query that includes the ... >I have created a Query and Report in Microsoft Access fir a "Single Level ...
    (microsoft.public.access.tablesdbdesign)
Loading