Recursive BOM

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

  • 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: Recursive BOM
    ... Thanks, I explored the nested sets solution, I found all the info ... The problem is the standard BOM problem. ... 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: 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)
  • Start With/Connect By problem
    ... I'm trying to help a colleague with an SQL problem; ... item in a Bill Of Materials (BOM) - and Start With/ Connect By seems to be ... Contract 'EMGA'. ... the query then only shows BOM entries for the top level. ...
    (comp.databases.oracle.server)
  • 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)
Loading