Emulate "infinate" subreports?

[sscolovos]

OK. I need a way to report data with many cascades having a train of an unknown number of generations.

Here's an example: You have a Project table. Each record holds data about one project. This project, though, can be a subset of another project. So, this project can have a great-grand parent, a grand-patent, a parent, a child, a grand child (you get the idea). I figure linking them isn't the hard part (I simply have a field storing the project's parent).

What's hard is reporting this if I want to show a project and all its offspring. Since the number of generations is varied, I can't design with a pre-determined number of subreports. What would be perfect is the engine behind Windows Explorer.

Thanks in advance for ANY ideas... I was hoping I could find some helpful 3rd party plug-in, because I see myself using this a lot in the future.

 

[Pat Hartman]

Look for a Bill of Material (BOM) example database. I have solved the problem but only for a fixed depth. You just keep adding instances of the self-referencing table to the query and connecting them child-to-parent until you have enough levels. I did 7 levels which was one more than my app needed. I don't know what the limit is. The real solution is a recursive subroutine. Good searching

 

[sscolovos]

Pat,

Thanks for the reply. You're definately on to something about the recursive subroutine. You are the second person to mention that. It looks like that is the way to go because the client is somewhat anal and demands and unlimited number of self references. (no matter how unrealistic). I believe the hardest part is merely knowing what one's problem is called (akin to going into a hardware store: "I need a thingy."). Now I can do a much better job searching. Thanks again.

Oh, would you please recommend your choice of a MS Access reference book(s) for the intermediate/advanced user? Its about time I bite that bullet, and there are a myriad of them out there. Thanks!
...Sym

 

[Pat Hartman]

It's been a long time since I was at Boeing, but I'm pretty sure that the BOM for a 747 is around 20 levels deep.

I like the Access Cookbook if you're looking for something to help you learn VBA.

 

[Stuart Heath]

Treeview Control

It may not solve your reporting issues, but the Treeview control provided in Access, gives you the functionality of the Windows Explorer engine to display/edit your own data. I beleive you can even download from microsoft a wizard for populating the control.