Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2010
    Posts
    5

    Unanswered: Help: Sum values up a tree

    I have a small MS Access DB that is in essence an accounting journal. But each record is flat except for links to look up tables. I want to be able to arrange the records in a tree and then sum the values from individual records to a top node.

    I don't want each record to identify its parent in the tree, because I would like to add nodes and move records from one node to another.

    I am very poor at scripts and VB for Access. It would be nice to somehow do this via SQL.

    Thanks.
    Lou.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I might utilize the dlookup command against a query (if not huge recordset) putting this function or dlookup command in the field's source and put the summing/criteria in the query itself (ie. basing it off of the same node's listing or the records you want to sum) (or write a function to open the recordset and retrieve the sum using whatever criteria.)

    I haven't worked with nodes but this is often how I approach multiple listboxes or summing a specific set of records to return the value to a field on any form. A function is ideal since it will be the fastest versus a dlookup.

    example of a function returning a value (you would then put =retSumOfXX() in the field's source).

    (note: ado code)

    Function retSumOfXX() as variant
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * ....you're query syntax with logic to sum - design it in query designer, change design mode to SQL, then copy and paste here....edit as necessary.
    rs.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly
    if rs.eof and rs.bof then
    retSumOfXX = null
    else
    retSumOfXX = rs!mysumField
    end if
    rs.close
    set rs = nothing
    End Function

    Example using dlookup:
    =Dlookup("[MySumField]","MySumQueryName")
    or
    =Dlookup("[MySumField]","MySumQueryName","[additional criteria logic here]")
    see dlookup help for more info.
    Last edited by pkstormy; 01-28-10 at 23:40.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by louarnold View Post
    I don't want each record to identify its parent in the tree, because I would like to add nodes and move records from one node to another.

    You lost me.

    Trees are defined by a lineage of parent/child relationships. You "move" nodes by changing the identified parent.

    What are you driving at here?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Jan 2010
    Posts
    5
    Quote Originally Posted by pkstormy View Post
    I might utilize the dlookup command against a query (if not huge recordset) putting this function or dlookup command in the field's source and put the summing/criteria in the query itself (ie. basing it off of the same node's listing or the records you want to sum) (or write a function to open the recordset and retrieve the sum using whatever criteria.)

    I haven't worked with nodes but this is often how I approach multiple listboxes or summing a specific set of records to return the value to a field on any form. A function is ideal since it will be the fastest versus a dlookup.

    example of a function returning a value (you would then put =retSumOfXX() in the field's source).

    (note: ado code)

    Function retSumOfXX() as variant
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * ....you're query syntax with logic to sum - design it in query designer, change design mode to SQL, then copy and paste here....edit as necessary.
    rs.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly
    if rs.eof and rs.bof then
    retSumOfXX = null
    else
    retSumOfXX = rs!mysumField
    end if
    rs.close
    set rs = nothing
    End Function

    Example using dlookup:
    =Dlookup("[MySumField]","MySumQueryName")
    or
    =Dlookup("[MySumField]","MySumQueryName","[additional criteria logic here]")
    see dlookup help for more info.
    Haha. I have to think about that for awhile, but I will get back to you one way or another. Thanks.

  5. #5
    Join Date
    Jan 2010
    Posts
    5
    Quote Originally Posted by Teddy View Post
    You lost me.

    Trees are defined by a lineage of parent/child relationships. You "move" nodes by changing the identified parent.

    What are you driving at here?
    You are not as lost as you think.
    In trying to explain myself, I found the following concepts:
    1) There is only one table. It's records belong only to leaf nodes.
    2) Each record identifies the leaf node it is associated with.
    3) Higher nodes have no records, but only values that are the sums of subnodes.
    4) Moving a record from one node to another, simply requires changing the parent node identifier.

    I know how to get a total of the values of the leaf node records with a simple SQL command. But how do I then move up the tree summing the values as I go?
    Last edited by louarnold; 01-29-10 at 14:37.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's painful to do in Access since it has no concept of hierarchal data types.

    I haven't tried to do this on my own, but I think the answer would be you have to do it by hand. That is to say walk the tree programmatically and either store the id's that you care about in some kind of temporary storage container so you can sum them with one query later, or keep track of a running sum while you go.

    What are you doing as far as generating and displaying the tree? Maybe there's another option to throw in some hooks earlier in the process.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jan 2010
    Posts
    5
    Quote Originally Posted by Teddy View Post
    That's painful to do in Access since it has no concept of hierarchal data types.

    I haven't tried to do this on my own, but I think the answer would be you have to do it by hand. That is to say walk the tree programmatically and either store the id's that you care about in some kind of temporary storage container so you can sum them with one query later, or keep track of a running sum while you go.

    What are you doing as far as generating and displaying the tree? Maybe there's another option to throw in some hooks earlier in the process.
    well, to further this design, I thought of a 2nd table with the following fields:
    - A node number (identifying this node)
    - A parent node number (there may be many subnodes to a parent, but each record is linked to only one parent)
    - A subtotal of all subnodes to the current record.

    You are correct though; one would have to procedurally iterate summing up the tree. I can't think of any SQL statements that would do that without being in a procedure.

    And MS Access is not necessary. One could simply write this application in Java, lets say.

    But I can't help but think that there is a very obvious, simple way that just has not yet hit me.

Posting Permissions

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