Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2002
    Posts
    75

    Unanswered: Report Based on Recursive Data

    Hi everyone!

    I need to create a reports based on the data from only one table, but the data in that table is, so to say, self-related: I have three major fields that represent relations between records (there other fields in the table as well, but they are not relevant to the problem): a) [ItemID] (autonumber), b) [RollsUpTo] (number, used to indicate relation to higher-level record--eg. RollsUpTo 102 means that the record is the "child" of ItemID 102), and finally c) [Level] that represents the hierarchy of the record. To be more clear, here is the example of the data in the table:

    ItemID.......RollsUpTo... Level
    102...........NULL............0
    96.............102.............1
    2.............. 102..............1
    55..............96..............2
    50..............96..............2
    41................2.............2
    38................2.............2
    73..............96..............2


    I need to create a report to display data in a hierarchical format:

    102
    ......2
    ..........38
    ..........41
    ....96
    ..........50
    ..........55
    ..........73

    How should I construct my recordsource queries? Is having nested reports the only choice here?Or I need to create several dynamic recordsets in VBA and then populate the report in its Open event? I am totally blank...

    I do understand that the data would be easier to work with had it been normalized and divided into several one-to-many related tables, but it is a small (165 records) and rather static table; moreover all the records--both child and parent--have the same attributes, so having them separated into two tables would be a total redundancy of attributes.

    Hope it does make sense... Thanks in advance for your input!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I've not done this before, but it is possible through some nested SQL. I'll have a hunt around and see if I can find where I read up about this.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Apr 2002
    Posts
    75
    Thanks, StarTrekker!

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Failed to find it.... I'll try again when I am feeling better (rotten flu).
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    rudy
    where are you?
    this is right up your street.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its going to be something like

    select mytable.itemID as RootID from mytable as root
    left join mytable as level1 on level1.itemID=root.Rollsupto
    left join mytable as level2 on level2.itemID=level1.rollsupto
    where root.rollsupto=null
    order by.....

    as ever I cna't test it as I don't have a copy of Access on this PC.. thats my excuse and Im sticking to it

    that should be in the correct area
    you will need to play with the sort order to make it do what you want

    Im not convinced level IS required... it could be derived or indferrd from the query..
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vasilyok
    I do understand that the data would be easier to work with had it been normalized and divided into several one-to-many related tables
    sorry, this is not correct -- it's already quite adequately normalized, and having several tables is not easier to work with

    true recursive SQL is possible in SQL Server 2005 but this isn't SQL Server 2005

    all i can offer you is this: http://sqllessons.com/categories.html

    this will show how to write the SQL for N levels, where N is the number of levels in the hierarchy

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is an adjacency list hierarchy.

    You have two choices - if there is a realistic finite depth then use Mark's suggestion. If it is not then you need to use a recursive VBA function to build a query like Mark's, but dynamically. Access SQL has no inbuilt stuff for recursion.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - I was searching for that article. I thought you spelled "horsie" "horsey"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    clever pootle, searching google for some of those category names

    my article does turn up but you have to use the correct combination

    "carrot feldspar poodle" works, but "rutabaga chihuahua" doesn't (although it did lead me to this page which i have bookmarked for my own amusement)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    This is an adjacency list hierarchy.

    You have two choices - if there is a realistic finite depth then use Mark's suggestion. If it is not then you need to use a recursive VBA function to build a query like Mark's, but dynamically. Access SQL has no inbuilt stuff for recursion.
    Id agree its not a 'nice' solution, and it presumes a fixed number of levels.... I don't think its suitable for an open ended solution.. Ive used the code similar to Rudy's categories reference for up to 10 levels. failing that you are in loop processing or stored procedures
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    I don't think its suitable for an open ended solution..
    sure it is

    write the query for 11 levels, just show 10 levels, and beside each node that has an 11th level (i.e. not a leaf node) have a link that says "[more]" or something, such as if they click on it, you grab 11 more levels starting at that node for that subtree

    dead simple

    besides, any hierarchy that goes more than N levels (you pick your own comfort value for N) is a user accessibility nightmare anyway

    if you disagree, please give examples (i.e. urls) of real-world hierarchies that are more than 10 levels deep, which you think are a user accessibility dream

    i'll wait...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd agree you have to make a decision on how many levels you are going to retrieve

    I'd agree that the number of levels can make it difficult to represent in a meaning full manner.

    but I term it as not open ended as you need to know how many levels you expect and develop the query accordingly. as you suggest if you have more than that you have to requery. it ceases to be a one hit solution, it ceases to be open ended because if you have more levels than the query is designed for yu have to requery

    the SQL example I codged together works for 3 levels, the one in your example is for 10 levels. sometimes its difficult to know how many levels are expected in the final solution as its based on the user data.

    Ive seen production models that suggest you should never have more than 4 levels of assembly in a finished product, yet I've seen production planning for avionics which have many more than that.
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the recursive CTE solution in SQL Server 2005 is pretty elegant

    i can't find it at the moment, though
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, here it is

    http://msdn.microsoft.com/en-us/library/ms186243.aspx

    surprisingly clear writing for a microsoft article
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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