Iterative Query

[RichUE]

Our manufacturing database holds bills of material. I often need to run a
"where used" enquiry to find out which assemblies require a particular
component. The Business System has a built-in utility for this but it often
gives answers I'm not interested in. I'm only interested in components or
sub-assemblies that belong to a particular parent assembly. This parent may
be several 'levels' away. So I'm thinking that a useful extension of the
"where used" utility would be a "restricted where used" enquiry, where I'm
asking "where is this component used within that parent", so ignoring other
assemblies outside the area of interest.
I have a linked table called Bills, which contains a list of parent
assemblies and their child components. If I were to create a parameter query,
then I would enter the parent assembly part number and the component part
number. The query would then search the Bills table to find the first
generation parent(s) of the component. If any of the found parents match the
parameter then the result would be displayed, but if not then the parent part
number(s) that are found should (each) be used to run a second generation
query. If any result If any of the found parents match the parameter then the
result should be displayed, but if not ... (repeat). If the entered parent
assembly is never found, then a suitable message would be helpful, like
"<component> is not used on <parent>."
Is this achievable? Has it been done already?
I'm using Access 97 and I'm unfamiliar with VBA.

 

[Jeff Boyce]

Rich

Try searching on-line and at mvps.org/access for "BOM" and "bill of
materials" to get an idea how other folks have handled this. The functions
they've use will expect data in a particular structure, though, so be ready
to compare your data structure with theirs...

Regards

Jeff Boyce
Microsoft Office/Access MVP