Home

You've probably used a TreeView to view a hard drive directory structure before, you click on a folder and expand it to show the folders within that that folder. The top folder is the parent and the folder within it are the children. Each folder is a node. All nodes can have children (subfolders).

The following table shows how I would store a single parent node that has to child nodes.

This is an example of a a self-join table in a database, i.e. table that looks at itself. You could have an Excel table that looks like this, too.

I've created sample files for both a form in Microsoft Access and worksheet in Microsoft Excel. In the article I will be referring to a form in an Access database and populating data from a single table (the sample file also shows populating from multiple sources), but the method of adding nodes is the same; what differs is how you get the data used to populate the TreeView control.

In Microsoft Access, you can create running totals in reports by placing a textbox bound to the value field, and setting the 'Running Sum' property to true. You can set the running total to be a running total over the lowest level group (i.e. at the detail level) or over all the records returned for the query.

You can also do a running count by adding a textbox and setting its value to 1, and then add another textbox to do a running sum over that text box. Using a combination of the running total and running count, you can create a running average.

Ideally, a report is the to do these type of calculations. But there may be occasions when you need to have these calculations done is a query.

Microsoft has a KB article demonstrating how to accomplish running sums in query using the DSum() function. While this method works, it is extremely inefficient (i.e. slow).

Allen Browne: DLookup(), DSum(), etc are slow to execute. They involve VBA calls, Expression Service calls, and they waste resources (opening additional connections to the data file.) Particularly if JET must perform the operation on each row of a query, this really bogs things down.

A subquery will be considerably faster than a domain aggregate function.

[Note: The sample database includes a query (qryDomainFunc) that uses this method as a demonstration only.]

There is another way to accomplish a running sum using a correlated subquery. Using a subquery is more efficient (i.e. faster) then using aggregate functions such as DSum() and DCount().

If you've ever worked in a big spreadsheet with a thousands of rows of formulas, you may have noticed that Excel may recalculate when you change a value in a cell (or cells). Excel is supposed to be optimized such that only cells that depend on those cell(s) are recalculated. (See link section at the bottom)

There is an exception to that, i.e. Volatile Formulas. Volatile formulas are formulas that are recalculated every time any value anywhere in your workbook changes regardless of whether it affects the volatile formula or not. One such formula is RAND() - i.e. the formulas that generates a psuedo-random value between 0 and 1. Go ahead and try it. Put =RAND() in any cell in a worksheet, then type a value in another cell. You'll see that the value in the cell with RAND() will change. If you need a list of random numbers that doesn't change, you need to use RAND() to create that list, then copy and paste it back as values. Without knowing that RAND() is a volatile formula, you might not notice that any calculations that reference the cells with the volatile RAND() formula are changing often.