By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,806 Members | 1,490 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,806 IT Pros & Developers. It's quick & easy.

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

P: 1
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.
Jul 25 '06 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 1,418
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;"

End function

It's all!
Sep 6 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.