472,125 Members | 1,520 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,125 software developers and data experts.

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

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
1 4294
1,418 Expert 1GB
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.

Similar topics

2 posts views Thread by replace-this-with-my-name | last post: by
8 posts views Thread by annecarterfredi | last post: by
6 posts views Thread by zaina | last post: by
3 posts views Thread by NatRoger | last post: by
2 posts views Thread by Jim Devenish | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.