It is easy enough to create queries for this type of structure as long as
you know what the maximum depth is.  I've done 6 levels without problem.
This is actually the ideal structure for a TreeView control if that suits
your design plans.  If you write VBA to "walk the tree", you can make it
recursive and so process a tree with an infinite depth.

Three separate tables will be slightly easier to work with and easier for
most people to understand.  However, that is not technically the correct
design and would certainly cause a problem if there is any possibility at
all that the depth could increase.

Also, if you're accessing the database from outside Access, (for example
ASP) you can use Shape Queries.  AFAIK, the type of connection access has
with itself does not support data shaping.

HTH;

Amy

Dear Rupert:

I would recommend the self referencing one-table approach.

Be sure to have a column in the table that represents the levels in the
hierarchy.  For this, I commonly use an alpha-numeric description, placing
thise in a foreign table which then numbers the levels.  The numbers do not
have to be consecutive (nice if you need to insert more levels between
existing ones later) but they do need to represent the hierarchy.  It might
be like:

A    10
B    20
C    30

You will probably want a query that gives these and Ranks them.  The Rank
column is added by this query so it looks like:

A    10    1
B    20    2
C    30    3

Do not store this in the table.  If you insert levels later, it must
automatically re-rank them.

In the form for maintaining this table, a Parent row must be selected for
each element entered.  Note that, by your convention, the A level would have
no parent.

Now what you are creating is a tree structure.  It needs a root.  I would
add one:

Root 0
A    10
B    20
C    30

This can be the parent of all A level elements.  The value 0 should be used
to make this a "special" level.  No element can be added to the table at
this level.  The one and only Root entry must be made manually before user
can begin adding their own elements.

An important factor in determining how this will work is whether the tree
"skips levels" or not.  Is the parent of every level B a level A, or can
they exist under the Root?  Can the parent of a level C be a Level A?

There will be a UNION query that generates all the possibilities.  The
outcome of this query can be made to be an "indented outline" form.  This
form is probably the easiest way to explain what is being produced, so I'll
give a sample.

Say all the elements at level A are labelled A1, A2, A3, A4, . . .

All the elements at Level B are labelled B1A1, B2A1, B3A1, B4A3, . . .

Here, B1 is under A1.  The actual names do not have to look like this.  I
have chosen this only to illustrate.

All the elements at Level C are labelled C1B1A1, C2B3A1, C3B3A1, C4B4A3

The outline form for all this looks like this:

A1
 B1A1
   C1B1A1
 B2A1
 B3A1
   C2B3A1
   C3B3A1
A2
A3
 B4A3
   C4B4A3
A4

Again, the actual names attached would not look like these.  They might be
John, Mary, Pete, and Carol.  But the outline form above is the key to both
understanding what is built and how it functions.  You can not only get a
report to sort like this, but also to indent the rows as shown for ease of
interpretation.

This is a fair sized study, and I won't go into any more for now.  Let me
know if you want more.  Please present some more details of what you want,
and answer the question about skipping levels.  We'll go into the table
structure next, then the UNION query.  What happens will depend on your
responses.

Tom Ellison