UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Running Sums    
Running Sums

Often you will find that you have a value in your query that you want to see a running total for. To accomplish this, you can use the DSum() function to sum the values. The key is to use a field that is also being used to sort the query's data. The reason for this is simple: The DSum() function will be used to sum values that meet a criteria that basically says "include all values that appear in this query on or before the current record."

So, for example, let's say that you have the following table of inspections:

  • tblInspections
  • InspectionID [Autonumber, Primary Key]
  • dteInspectionDate [Date of the inspection]
  • curPassRate [A point value for the inspection]

Your query would be sorted by the date field, since you cannot count on Autonumber fields to be incremental. The date field is much more reliable in this respect.

To see a running total of the curPassRate values, you would include this expression:

DSum("curPassRate","tblInspections", "dteInspectionDate<=#" & [dteInspectionDate] & "#")

This expression inserts the current record's dteInspectionDate value between the octothorpes (the "#" symbols), thus creating an expression along these lines:

DSum("curPassRate","tblInspections","dteInspectionDate<=#10/25/2014#")

Since the comparison is "<=" the expression will include the current record's curPassRate value. If the comparison were "<" then the expression would include all of the records up to, but NOT including the current record's curPassRate value. You can choose the comparison based on what you want your running total to display.

For each additional group of data within a query, you will need to add a matching expression to your DSum's criteria. For example, if the inspections table included a lngInspectorID to track multiple inspectors, you would need to alter your DSum expression like this:

DSum("curPassRate","tblInspections","dteInspectionDate<=#" & [dteInspectionDate] & "# And lngInspectorID=" & [lngInspectorID])

Now the expression creates a running sum for each inspector. You would also want to add a sort to the lngInspectorID field so all of the records for each inspector would be grouped together.

It's important to note that if you are filtering your query data in any way, you will need to apply the same filter to your DSum expression. So for example, if your query is filtered to only show records where the curCost is greater than $5, you will need to make sure the DSum expression only sums values that are greater than $5 as well. Remember, the DSum function will be pulling data from a table, not the current query.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 2,449 times.  This page was last modified 13:15, 16 October 2014 by doctor9. Contributions by Jack Leach  Disclaimers