I need Recursive query structre that can .....

[ozcanseker]

I am trying to write a product-row material cost program. Every product consists of row materials. When I sum up cost of row materials of each product I can find cost of products. But when the row material of the a product is a row material again the my solution does not work.

My table is like that:

Product Row material
p1 r1
p1 r2
p1 r3
p2 r4
p2 p1

I have another table that has costs of row materials.

My query computes the cost of p1 but does not compute the cost of p2 since not knowing the cost of p1. I need a recursive structure that can compute cost of p2.

[PEB]

Hi,
Imagine that information is saved in my_table
Product Row material
p1 r1
p1 r2
p1 r3
p2 r4
p2 p1

For exemple you want to get the row material for p2 in rows, result like this:

p2 r1
p2 r2
p2 r3
p2 r4
p2 p1

In one query, without using code it seem quite difficult...

But if it's an important thing for you You can do the follow:


1. Create a table with those Product, Row_material, My_Flags! Save it As My_Results
2. Create an append query that appends those
p2 r4
p2 p1
in your table

3. Create the folloing function in module in Access Basic:

Function check_for_products()
dim i

If isnull(dcount("Product","My_Results","((Row_materi al) IN (Select Distinct [Product] FROM my_table))")) then exit function

docmd.Runsql "UPDATE My_Results SET My_Flags = True WHERE ((Row_material) IN (Select Distinct [Product] FROM my_table));"

docmd.Runsql "APPEND INTO My_Results (Product, Row_material, My_Flags) SELECT Product, Row_material, False AS Expr1 FROM my_table
WHERE ((Product) IN (Select Distinct [Row_material] FROM My_Results WHERE My_Flags=True));"

docmd.Runsql "DELETE FROM My_Results WHERE My_Flags=True;"

i=check_for_products()
End function

It's all!