Printing Hierarchal Data

[Craig Hornish]

Hi,
I have 2 related questions, but will have 2 posts as they can be
mutually exclusive.

How can you print Hierachal Data in reports? I have tried multipe
copies of a table in a query, but because in one set of tables that I have
the levels could be unlimited I can't use this techique. I have thought of
other ways of doing this but don't want to limit you in your responce. But,
if you want to look at one other atempt you can look at Printing a Recordset
Not Bound to a Report.

Thanks
Craig Hornish


Sample Table
ID Name ParentID Type
1 Level1-A Null 1
2 Level2-B 1 2
3 Level2-C 1 2
4 Level3-D 2 3
5 Level3-E 3 3
6 Level3-F 3 4

Printed result - output - will also be modified depending on the Type (i.e.
Bold, larger font, and possibly the indentation)
A
B
D
C
E
F

 

[Allen Browne]

Craig, I don't think there is a simple answer to your question.

There is a finite number of grouping levels in Access reports, so it is not
just a matter of using sorting'n'grouping.

Attempting to resolve this kind of data with SQL statements is cryptic. If
you want to try that path, read Joe Celko's stuff, e.g.:
http://www.intelligententerprise.com/001020/celko.shtml
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html

The other alternative is to cheat and programmatically resolve the data into
a non-normalized temp table that actually contains the records ready for the
report. This also means setting a finite number of levels, but if you don't
do that you have a real problem with infinite recursion (where a child ends
up being its own parent), and you can solve that problem in the code the
limits the resolution to a finite number of levels. That's certainly the
simplest approach.