MS Access DSUM Function

The DSUM function in Microsoft Access is a great way to get quick and easy statistics from your tables and queries. This article will show you how to use DSUM to sum a range of values and place the result on a form in your database.

First, we need to get a handle on the data we're working with. The sample data we'll be using is in a table called, "tbl_Names". Here's a peek:

Figure 1

In this table, you'll notice we have a field called "chk", holding a checkbox, and a field called "rnum", which in this case has the number 10 for each record. This will help us easily add up and verify our result. We'll be using the Access DSUM function to total up the numbers in "rnum" depending on whether the record has a check or not. Since there are 6 records with checks, the result of our DSUM function should equal 60.

The first thing we're going to do is construct our formula in a textbox on a form. The arguments for a DSUM function are fairly straightforward. The first argument in this case is, "[rnum]", which is the field we'll be totalling. Next, is the table, "tbl_Names". And finally, there's the criteria, "chk=TRUE".

Figure 2

In plainspeak, we're saying, "Look at the rnum field in tbl_Names, and total up whatever's in there as long as that record's chk field has a checkbox in it."

Figure 3

Our result? 60. The DSUM function is very handy when you want to calculate values in a table or query and display them on a form, but you don't want to go through adding a subform, dealing with another recordsource, or building alternate queries for your calculations. Functions like DSUM, DLOOKUP, and DLAST aren't the speediest functions in the world - you may notice Access gives you a slight delay before they display on a form, but they are extremely handy for all sorts of calculations. Use them often and use them wisely!

David Badurina, President of Blue Moose Technology, LLC, is a relational database design expert. David's unique ability to easily explain virtually any technical concept has allowed him to work with companies such as AMD, Motorola, the American Heart Association, and countless small businesses. Learn more about database design right now at http://www.bluemoosetech.com.

Article Publishing and Re-Print Guidelines

The article text, resource box, URLs copyright information and these guidelines must be left intact and unchanged.
When re-published online, all links must be live hyperlinks.
These articles may not be distributed in any manner that does not comply with federal communication guidelines. Be Good!
These articles must not be password protected or limited to membership or paid-only viewing. Keep 'em free, folks!

You can easily link to this article!

If you'd like to link to this article from your own website, copy and paste the following HTML code onto your page:

On your page, the code will appear like this:

MS Access DSUM Function