No problem. It's just that the whole topic of representing hierarchical data in a relational environment is more that can be adequately addressed in an on-line forum like this.

My interest in suggesting the Treeview was that data model is a fully generic one for hierarchical data.

My concern is as follows. You have a data design that worked with one assembly. You're trying to make it more general by enhancing the table with additional fields. But, there always seems to be something else to add.

The simplest thing would be to enhance that basic table if you can make it flexible enough for all the various assemblies that you have. You would only have to modify in the small the data model, the interface could stay the same. However, you would have to do a lot of work to make your reports work. Again, I'd suggest that using old Fox commands would work better than SQL.

In processing the report, I'd work backwards. I'd take all the lowest level assemblies (let's say, 10th level) that are populated and then find their link at the 9th level, and include all 9th level assemblies that start at that level, and continue to work up. It may be helpful to maintain a table of relevant assembly information and quantities so that you can multiply quantities at the lowest subassembly level with the quantity of them used at a higher level.

If, however, you find yourself overwhelmed by enhancing the data model, you may want to start fresh with something that will work generically. The TreeView maintains each level entry as a separate record. The record contains caption information as well as links to the previous level.

Adapting that to your needs would give something like the following data model:

Assembly table
1. Assembly ID
2. Assembly name
3. Level
4. Level Name
5. Parent Assembly ID

All assemblies, subassemblies and products would fit into that model.
The top level of a product, assembly and subassembly would not have a parent assembly ID and would have a level of 1. Any levels higher than 1 would indicate components of the assembly, subassembly, or product. Since there is no distinction made between any of these, an assembly can contain a subassembly identified elsewhere.

You might want a separate table to identify products as distinct from subassemblies -- for picklists, etc.

This would provide the most general solution for you. However, this would require rewriting interfaces, you'll still have to work with recursive queries, etc.

Again, I want to emphasize, that you have a complex problem on your hands, and either way you go, you'll have a complex solution -- there's no silver bullet.

Let us know how you're able to proceed. If you need more specific info on the text above, let me know. I've tried to paint a high level picture, but there may be some areas that need to be filled in.

Hope that helps...

>Thanks but Im stuck with FPD25