Create a BOM query

[Jasper Recto]

I am trying to create a query that drills down and creates a parts list.

We have a database that has their parts in one table (tblParts). There is
another table that has the parts materials (tblPartMtl).

The way it works is that we have a top level part. That part has other parts
that go into it. Those other part can be comprised of smaller parts, and so
on ...

The Parts table is just a master list of each part. The Parts Material table
is the same list of parts plus what materials it is comprised of.

Now, one of the Parts Material could have it's own BOM. I would have to look
at that part from the Parts table and find out what its materials are
composed of.

What I would have to do is make the following links:

Part - PartMtl
Then link the PartMtl back to another Part table but using the part Mtl as
the linking key.

This can go back and forth 10 times or more to get a complete parts list.

Is there a better way of doing this?

Hope this makes sense!

Thanks,
Jasper

 

[Arvin Meyer [MVP]]

Have a look at this at the Access Web:

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