Showing Tree Data Structure on report

[David M C]

Here's my data structure:

tblWorks:

WorksID (Primary Key)
ParentID (if 0 root node, else child node under WorksID)
OrderID (the order to display the child nodes under the parent node)
WorksName (Descriptive name for the node)

tblWorksDetail:

WorksID (Foreign Key)
WorksDetailID (Primary Key)
WorksDetailName
Price

Now the problem:

I use tblWorks to populate a treeview control on my form. The currently
selected node determines which tblWorksDetail records are shown in the
subform. Nice and easy, and I can even get summary information to display for
the currently selected node (ie, sum all the child nodes).

Now I want to be able to print this data using a report, showing:

WorksName
WorksName
WorksName WorksDetailName Price
WorksName
WorksName WorksDetailName Price
WorksName WorksDetailName Price

etc etc.

The thing to note is that WorksDetailID can only exist when it's related
WorksID has no child nodes (this is a business rule handled using VBA). Any
ideas how I can get the report to work? The best I can do so far is to
recursively return a string detailing all the parent nodes. ie:

WorksName\WorksName\WorksName WorksDetailName Price


Thanks,

Dave