Bill of Material (BOM)

[Tonyi]

I have been trying unsuccessfully to find a reference or example to show how to do a BOM.
I understand it is a wellunderstood problem, but...
Specifically, I have table with AssemblyNo, PartNo, Quantity. Problem is that a Part Number may also be an assembly with its set of parts, and this may have several levels.
I need a query to list all parts (and quantities.) as a single level.

I would very much appreciate any pointers.
Cheers

 

[KKilfoil]

have a look at http://www.access-programmers.co.uk/ubb/Forum1/HTML/007463.html

Let me know whether or not this is close to what you need.

 

[Tonyi]

Thanks for reply. I have worked through your reference, and this is pretty much what I had done. Where I come unstuck is the point where "some recursive VBA code" is required.
The structured part list is working well. But to get it to a single level "purchasing list" is the problem.
Obviously, the partlist for the subassembly needs to be added to the main assembly partlist.
I have not been able to find good references on this aspect.
Cheers

 

[KKilfoil]

Sorry for the belated reply, but if you are still working on this problem, you might look here:
http://www.mvps.org/access/modules/mdl0027.htm

 

[Tonyi]

Thanks for example. Essentially this copes with the problems.
As yet I have not gone through it fully but this example produces a table. I wish to do this as a query only. Looking at the method, it does not seem easy to change it to operate as a query.
Is this possible, or do I have to work through a table.
Cheers