Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2007
    Posts
    6

    Unanswered: Displaying hierachical data in access

    How can i display hierachical data in access?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can do it with a query, or perhaps a report
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    or VBA and SQL if required.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jul 2007
    Posts
    6
    Could you please give me an example

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    example of hierarchical data, and the queries to go with it: Categories and Subcategories
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697

    Hierarchy tip

    On another thread, you asked about doing a hierarchy.

    Here's a tip: One tricky aspect of a hierarchy is "how do I find all nodes that are indirectly a child of x?" There is a reasonably efficient way of doing this.

    Given a table of parents and children:

    CREATE TABLE ParentsChildren (parent as INTEGER, child as INTEGER, PRIMARY KEY (parent, child))

    We can maintain a table of ancestors and descendants:

    CREATE TABLE AncestorsDescendants (ancestor as INTEGER, descendant as INTEGER, PRIMARY KEY (ancestor, descendant))

    (Yeah, those tables should both have self-referential foreign keys.)

    Suppose we insert a row as such:

    INSERT INTO ParentsChildren VALUES (x, y)

    What we want to do is find the ancestors of x and add y as a descendant of them.

    INSERT INTO AncestorsDescendants SELECT ancestor, y AS descendant FROM AncestorsDescendants WHERE descendant = x

    It's that easy. Deleting a node is also simple, you just remove all references to it.

    It's also trivial to prevent cycles.

    Tradeoffs: Code complexity is greatly simplified because SQL sucks at tree traversal. Time and space requirements aren't bad because, in practice, trees are almost always shallow. You do need to use triggered procedures to make sure updates happen, of course.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •