Using the DSum Function

Today we are going to look at using the DSum Function within a Calculated Control to produce a Total Amount. The DSum Function works in a similar way to the DLookUp function which I wrote about in my last post. Both functions pass an identical set of parameters in their syntax. However, instead of looking up a field value in a table or query, the DSum Function calculates the sum of values contained in a particular field of a specified table.

So lets take a look at the syntax of the DSum Function:

=DSum("fieldName", "tableName", "criteria")

The first parameter we pass is the name of the field containing the group of values that we wish to add together as a sum.  The second parameter is the name of the table or query, and the third parameter is an optional criteria used to restrict the group of records upon which the calculation is to be made.  All the parameters are passed as strings - hence the quotation marks.

So, for example, if we had a field called Amount, and a table called tblCategories, our expression would look like this:

=DSum("Amount", "tblCategories", "fldCategory='A'")

The final parameter is the criteria string. In this example the criteria restricts the calculation to records containing the value 'A' in the fldCategory field.  As such this function would produce a Total Amount for all records allocated the category value 'A'.

So lets put all this into practice.  In figure one below I have created the tblCategories table and populated it with values.  There are four different categories A,B,C and D.  Each instance of a category has an amount value associated with it.

Figure 1: The Categories Table (tblCategories)

I then created a form with four Calculated Controls.  I used the DSum Function in each control to calculate a Total Amount for each category.

Figure 2: The DSum Function within Calculated Form Controls.

And here are the results:

Figure 2: Results of the DSum Function.