Multi-Level BOM (Bill of Materials)

[Meredith]

I have a dilemma with a multi-level BOM which has plagued me for months. I
use linked tables which are in a data warehouse which I have no control over
so the tables/fields cannot change. The table I'm using has the following
fields:

Part Number
Component Part Number
Quantity

I need to be able to input the highest level Part Number, and the query show
all of the parts below (which includes up to 10 levels of parts.) I am not
concerned with quantity, cost or anything along those lines. I only need the
part numbers.

In my many attempts to create this query, I always manage to loop back into
infinity and usually just give up. I have tried studying up on the Celko
Nested BOM example, but I don't think I'm going to be able to use that with
the tables I have available to me.

First of all, is this even possible? And if so, how? =)

Much thanks!
Meredith

 

[MGFoster]

I have a dilemma with a multi-level BOM which has plagued me for months. I
use linked tables which are in a data warehouse which I have no control over
so the tables/fields cannot change. The table I'm using has the following
fields:

Part Number
Component Part Number
Quantity

I need to be able to input the highest level Part Number, and the query show
all of the parts below (which includes up to 10 levels of parts.) I am not
concerned with quantity, cost or anything along those lines. I only need the
part numbers.

In my many attempts to create this query, I always manage to loop back into
infinity and usually just give up. I have tried studying up on the Celko
Nested BOM example, but I don't think I'm going to be able to use that with
the tables I have available to me.

First of all, is this even possible? And if so, how? =)

Look at this site for an example of BOM in Access:

http://www.mvps.org/access/modules/mdl0027.htm

 

[Meredith]

Thanks for your response MG =) I have tried this example as well, but the
output is not what I'm looking for. What I need is an all inclusive, simple
list of part numbers from all part levels including and below the highest
level part. I will try show you how I see it in my head (I hope the
formatting stays):

Level Part Number
1 ABCD <--Parent
2 1234 <---Child
2 5678 <--Parent (9012 and 3456) and Child (5678)
3 9012
3 3456
1 ZYXW
2 9876
3 5432
3 1987
4 AB12

What I need is a list that will give me all of the part numbers, regardless
of level in one single list.

Parts
ABCD
1234
5678
9012
3456
ZYXW
9876
5432
1987
AB12

This seems so simple in my head but for the life of me, I can't figure it out.

I do appreciate any help you can offer.

Mer

 

[John Gray]

Hi,

I think a little more information is needed...

What's the linkage between [Part Number] and [Component Part Number]? ie: how would I
know which Component Part goes with which Part? There must be something in the table
that indicates this.

Are "part levels" indicated in the table? If so, how?

Any other pertinent fields we should know about?

but the output is not what I'm looking for.

So what *are* you getting? And, what does the query look like?


John


----------------------------

"Meredith" wrote

Thanks for your response MG =) I have tried this example as well, but the
output is not what I'm looking for. What I need is an all inclusive, simple
list of part numbers from all part levels including and below the highest
level part. I will try show you how I see it in my head (I hope the
formatting stays):

[...]

 

[John Gray]

Hi,

I think a little more information is needed...

What's the linkage between [Part Number] and [Component Part Number]? ie: how would I
know which Component Part goes with which Part? There must be something in the table
that indicates this.

Are "part levels" indicated in the table? If so, how?

Any other pertinent fields we should know about?

but the output is not what I'm looking for.

So what *are* you getting? And, what does the query look like?


John


----------------------------

"Meredith" wrote

Thanks for your response MG =) I have tried this example as well, but the
output is not what I'm looking for. What I need is an all inclusive, simple
list of part numbers from all part levels including and below the highest
level part. I will try show you how I see it in my head (I hope the
formatting stays):

[...]

 

[chuck.streb]

Look here ... http://www.mvps.org/access/queries/qry0023.htm