Recursive Query

[Ben Scaithe]

I am trying to figure out the best way to build a query that will list what
I need. I have a single table that has the following fields:



ProductID

IngredientID

IngredientQuantity

IngredientType



There is no Primary Key in this table. The main focus of the table is the
ingredient that makes up the product, so ProductID is repeated in several
rows, as so:



ProductID IngredientID
IngredientQuantity IngredientType

1 100 10
Simple

1 101 3
Simple

1 102 2
Complex

2 101 3
Simple

2 103 7
Complex

102 110 3
Simple

102 111 1
Complex

111 156 4
Simple

111 157 2
Simple



If the IngredientType is "Complex", then it is a product within the product;
it is also listed in the table and has ingredients of its own. For example,
Ingredient 102 is complex, so it is also listed with its own two
ingredients, 110 and 111. Also, 111 is complex as well, and has the
ingredients 156 and 157.



What I need to build is a query that lists the product along with all of the
base ingredients and how much of each. For example, the resulting rows for
Product 1 would look like this:



ProductID IngredientID
IngredientQuantity

1 100 10

1 101 3

1 110 3

1 156 4

1 157 2



Instead of showing 102, it shows its ingredients. Same for 111.



Does anybody know what would be the best way to get the results I need?
Thanks for any help!

 

[Allen Browne]

Your ingredients are nested inside each others.
Search for help on Bill of Materials, e.g.:
http://www.mvps.org/access/modules/mdl0027.htm

 

[Tom Ellison]

Dear Ben:

The subject is one of considerable interest to me. Some day I should
probably write a book on this one.

Here are a few basic pointers.

If possible, create a table and name "levels" of this hierarchy.
Right now you have Simple and Complex. For purposes of illustration,
let's split up Complex into several levels so we have:

Simple 1
Sub-Assembly 10
Assembly 20
Finished 30

We have a rule that any item may be made up of pieces that are at
lower numbered levels. Never allow anything to be made up of pieces
at the same or higher levels. This would be an impossibility. I you
use 2 of piece 'A' to make piece 'B' and 3 of piece 'B' to make piece
'A' then you'll never build either. This enforces a 'tree structure'
to the data.

Now, you can then lookup all the components to build anything with a
UNION ALL query, which would have to have 6 separate SELECT sections.
One would show all the Simple components of every Sub-Assembly.
Another would show all the Simple components in every Assembly. A
third would show all the Simple components in every Finished product.
The fourth would show every Sub-Assembly in each Assembly, the fifth
Sub-Assemblies in Finished products, and the sixth every Assembly in
Finished products. If you added a fifth level, you would have to have
4 additional sections of UNION ALL query, so there is a practical
limit of 6 or so levels, but that's enough complexity to handle a
great proportion of such problems.

Make any sense? Now how do you see all the Simple components that
make up all the Sub-Assemblies in an Assembly? With another SELECT in
the UNION ALL query. For 4 levels you have 8 components in the SELECT
query. For 5 levels it is 16, and it doubles each time you add a
level.

There are no truly recursive methods available, but by structuring the
data you can avoid cycles (where A builds B and B builds A) and define
how the UNION ALL must be built.

That is a very sketchy starting explanation of how I do this. Think
on it and get back if you have questions.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts