Hierarchical Queries

[angiras]

where can I find advanced example for Hierarchical Queries ?

id_category
level
id_category_sup
category_name


thank you

[MAK]

http://www.cise.ufl.edu/help/databas.../queries4a.htm

http://oldlook.experts-exchange.com/..._20785690.html

[xordevoreaux]

To help those looking for hierarchical queries in this MS Access forum: MS Access and ADO provide ADOMD, which is the multi-dimensional data shape provider available to users of MS Access/VB.

The ADOMD data shape provider has its own syntax, in which one of the fields of a query is a recordset pointer identifying related child records.

The structure and format also permit aggregate calculations, including sum, avg, and others, for the child recordset, and you nest the hierarchies, if necessary.

A very good reference for datashaping hierarchical queries that you can exploit using MS Access is published by WROX: "Professional ADO 2.5 Programming"
ISBN 1-861002-75-0


If you're looking for MS Access 2003 references, the .Net ADO environment is entirely different, with different data provider capabilitities, and I haven't explored its multi-dimensional functionality yet.

[angiras]

yes I an using ado.net but my main problem is a structural problem : to build categories tables

If you have a multi level category and use

id_category
level
id_category_sup
category_name

then no foreign key and it is not very practicle

if you uses as many tables as level it is difficult to put any object in another category than the lower level

I have tried also

to build a column in this way

1_12_4_78 to get each level but nothing is really practicle

thanks for helping and for the links

[xordevoreaux]

Use a linked list to get away from table after table for each node on your hierachy.

I have a hierarchy system that I keep up with managers and the people that work for them. Instead of keeping up a separate President table and Vice President table and Director table all the way on down the list, I have two tables. One is just a Person table (tblPerson) that supplies the PersonID for the list. The other supports the list itself (tblHierarchy).

Hierarchy table looks like this:

HierarchyID (autonumber) (key)
PersonID (key) (required)
ParentHierarchyID (long integer)

The ParentHierarchyID is another HierarchyID in the same table. The ParentHierarchyID is not required.

For the president of the company (top of the hierarchy), his HierarchyID is 1 and his parentHierarchyID is 0.

vice president #1 underneath the president is HierarchyID 2 with ParentHierarchyID 1.

vice president #2 underneath the president is HierarchyID 3 with ParentHierarchyID 1.

On down the list.
Traversing the list is done by iterating a loop in vb or creating parent/child queries and using those queries as recordsets as fields in an ADOMD structure.