-
Hierarchical Queries
where can I find advanced example for Hierarchical Queries ?
id_category
level
id_category_sup
category_name
thank you
-
-
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.
-
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
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|