Creating a Bill of Materials Query

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I work for a company that has a multilevel Bill of
Material. I am trying to create a query process to show
all the items and their respective levels within the
Parent / Child structure. The baseline tables (Lilly's
Visual Manufacturing) contain the data one level at a
time. One table, all the data but not displayed as they
are linked within the Bill of Material. I would need to
run each level but in the end tie them all back into one
list. Each level creates the next table or level to once
again run the search against - and so on and so on.

Any thoughts?

Regards,

Mike Holthe
(e-mail address removed)
(304) 848-5529
 
If I understand the problem, you have a multi level BOM. Meaning

Assembly A consists of Component1, Component2, Component3...
(Seperate record for each component tied to assembly)
Component1 may also be an assembly with it's own set of components,
which may in turn be assemblies with components...

ALL of that information is in ONE table. You have nothing to determine
what level somethign may be at. Yck.

Any one item# may exist in a COMPONENT field, , or an ASSEMBLY field, or
both.
OK, how is this:

Add a field for BOMLEVEL. Set them all to One..
Now run a series of queries to do the following.

For every Component LEVEL ONE that has a matching ASSEMBLY, (meaning it
is also an assembly,) set the level to TWO.
For every Component LEVEL TWO that has a matching assembly, set the
level to THREE.
For every Component LEVEL THREE that has a matching assembly, set the
level to FOUR....

Continue until you get no more hits. At that point, the highest level,
(say four) will be those assemblies that are NOT also components, and
are therefore first level. Each lower number will be the next level.

It is conceivable that what is a third level component for ONE assembly
will be a second level component for a different assembly, but as long
as you sort by the new level number, all should be good.

Make sense? I hope?



..
 
Fred,

First of all, thank you for taking the time to review and
respond to my posting. Your understanding of the BOM
structuring is correct. Based on that, you also know that
the structures are dynamic and subject to Engineering
Change. If I understand your proposal correctly, I would
manually (?) assign values in a table that I would create
and then recreate the table and update it for each for
each level / as I rerun the query (a series of Make
Tables?).

A few things come to mind: 1) I am a self
taught "average" Access user, I cannot conceive how to do
what you suggest without manually doing it - I have
played with IIF statements, other formulas and criteria,
2) our BOM's are very complex and go as far as nine
levels deep, 3) our main customer proposes Engineering
Changes "often" 4) I try to create processes that may be
set up intensive but run on command once completed.

Am I missing the boat on your suggestion? Either way, I
truly thank you for your input!

Regards,

Mike Holthe
304-848-5529
 
"If I understand your proposal correctly, I would
manually (?) assign values in a table that I would create
and then recreate the table and update it for each for
each level / as I rerun the query (a series of Make
Tables?)."
Nope. If you are not allowed to mess with the original table, start
with a maketable query that copies all of the data over, as well as the
additional BOMLEVEL field. You can do this by entering into a new field
(after selecting all of the other

BOMLEVEL:1

Which will create a field called BOMLEVEL in teh new table and prefill
the fields with a ONE.

Then build each query to raise the BOMLEVEL. Finally, throw all of
those into a macro, (make sure you get it all in the right order), so
that it is a one step proccess. This allows you to rerun it at any
time.

" I have played with IIF statements, other formulas and criteria,"

Don't need it. I am relying on self deselection. It is basicly a form
of sort routine. Each succeeding query You can do the maketable
query.promotes those entries it needs to, and leaves those that don't
behind.

Then build a select query. Put the new table in twice, joining the
Assembly on one with the components on the other. This will give you
all components that ALSO have an assembly.Change it to an update query.

for the first query: [ For every Component LEVEL ONE that has a
matching ASSEMBLY, (meaning it is also an assembly,) set the level to
TWO.]

put ONE in Criteria under BOMLEVEL for the COMPONENT linked side, and
TWO under the update cell of the SAME field.
Save that as "updateONEtoTWO"

Then for the next query, change the ONE in Criteria under BOMLEVEL for
the COMPONENT linked side to a TWO, and change the TWO under theUpdate
cell of the SAME field.to a THREE. Save that as "update TWO to THREE".

Lather rinse repeat.

Then create a macro to run each query starting with the maktable, then
"updateONEtoTWO", then "update TWOtoTHREE"., "updateTHREEtoFOUR"...

Then let engineering screw around with everything. when they are done,
just run the maco. It will create a new table, recalculate all of the
levels, and you will be on your way. (By the way, I am using WORDS like
ONE TWO THREE to eliminate confusion. I suggest you use 1,2,3, for
sorting purposes.

Each query will raise a smaller number of components

Now, their may be an issue with those assemblies that are NOT
components, meaning the first level. It is Friday, and my brain is not
fully functional. IF so, you may need to update those seperately with a
final update query. You would do this with the find unmatched query.
Once again, use the two tables, link assembly to component, to give you
those records from the ASSEMBLY joined table without matching COMPONENT
joined table. At the final stage of teh wizard, choose "modify query",
run it to make sure it works, then change it to an update query. Put
NINE, or whatever the highest level will be in the BOMLEVEL of the the
assembly records. This will be the last query to run.

Play with it a bit. It will probably be much more clear when you start
building the first queries.
Realize this is all off the top of my head after a long week, so it may
need to be tweaked a little bit, but once you start workign with it, I
think you will get the idea.
 
Back
Top