ms access reports and queries

Question

Good day

I am creating a bill of materials program.

There are two main tables named Products and Sub_Products.

In the Products table, the fields are (Product_Name, Code). In the Sub_Products table, the fields are (Code, Sub_Name).

The tables are linked with code, i.e.: one product is made up of many sub_products, each sub_product is a product as well, making it have many sub_products.

I have created a query that reads a product and gets its sub_products. I need a query to compare Sub_Name with Product_Name and then check more sub_products, continuing until no more sub_products are found.

I created a query that gets product_name than gets component_name, than another query to change component_name to product_name and get its component

So every time I have to manually create a query to see the next level, is there no other way..

Quert sql statement for 4th Query---------

SELECT [Query3rd].Component_Name, products101.Our_Product_Code, products101.Product_Name, Sub_Products101.Component_Name FROM (Query3rd INNER JOIN products101 ON [Query3rd].Component_Name=products101.Product_Name) INNER JOIN Sub_Products101 ON products101.Our_Product_Code=Sub_Products101.Our_Product_Code WHERE ((([Query3rd].Component_Name)=(products101.Product_Name)));

Answer 1

See my reply to your previous question. Which has now been deleted.

This is the standard Bill of Materials problem. See Modules: Bill Of Materials although I'm sure there are many other examples out there.

Answer 2

If you stick with the Adjacency list model that you are currently using then yes, there no other way.

Your problem would be solved if you switched to the nested set model but there would be other trade-offs.

I suggest you read the book 'Trees and Hierarchies in SQL for Smarties' by Joe Celko and make an informed decision.

Answer 3

To solve this problem in the past I would use a big temp table with fields like level1, level2, level3, etc. Level1, of course would where you would put the "root" objects...that is, objects that don't have a parent.

Then you create a function that calls itself recursively and fills out the table into all of the levels. It is sort of yucky but it works.

It has been a while since I did that ... I will see if I can find the function somewhere but I doubt I will be able to get my hands on it.

But I bet someone else has that code more available. Anyone? Anyone?

Seth

PS...BOMs display great in a treeview control.