DSUM() function –The Art of Writing a Criteria : 1

As you learn more in MS Access, you will find that DSUM() function or for that matter any other domain aggregate functions such as DAvg(), DCount() etc. have interesting applications in data analysis. Now, these functions are very easy to understand in terms of what they do but the only confusing part is the criteria part of the function. I have seen many people including myself getting a bit uncomfortable and making silly mistakes while constructing the criteria in this function.

In this post, you will learn what exactly does DSUM() function do and more importantly how to construct the criteria properly. So, lets begin……

What is DSUM() and what does it do?

DSUM() function as the name suggests is a function which helps you to sum the field (i.e. a column) specified in a particular table based on a certain criteria, if required.

The function is as follows :

As can be seen from above, there are two ways in which you can write a DSUM() function:

1. Construct a DSUM() function with no Criteria
2. Construct a DSUM() function with a Criteria

Lets look at each of the two ways of working with DSUM() function.

DSUM() with No Criteria

In order to understand how a DSUM() function behaves when no criteria is specified, let us have a quick look at the following example :

Lets say, you want to sum the [Quantity] field in the table ‘Order details’ to see the total units ordered.

On running this query, you’ll get the following result:

DSUM() With Criteria

In order to understand how the DSUM() function behaves when a criteria is specified, let us first analyse the criteria part of the DSUM() function.

In the above snapshot, in place of = sign, you can also use other comparison operators like <,>,<= or >=.

Further, there are three types of criteria that can come after these comparison operators. Those are :

1. Text
2. Number
3. Date

DSUM() with Text Criteria

Let’s consider a scenario where you want to determine the total shipping fee paid across all the cities to which you have shipped your products. In order to do so, you’ll refer the table ‘Orders’ which holds all the related details of the shipped orders. The details you would need are:

• Field to be summed : [Shipping Fee]
• Table : Orders
• Other fields required : [Ship City];

You are somewhat expecting a similar output as follows:

 City Shipping fee Total City 1 450 City 2 555 City 3 687

The query will be constructed as follows:

As can be seen from the screenshot above:

1. You drag the field [Ship City] in the query grid.
2. Then, to begin with, just specify the DSUM() function without the criteria. On running this query, you’ll get the following. (Don’t forget to click the Totals button)

The total sum of the entire field [Shipping Fee] will come across each of the cities. BUT, this is not what you want. You want the total shipping fee paid across EACH city.

3. So now, you introduce a criteria in the DSUM() function by referencing directly to the field [Shipping City]. By referencing directly to the field, you are telling DSUM that for say a city A sum up the shipping fee corresponding to that city A only. Similarly, do the same for all the other cities present in the data. (Note the field [Shipping city] that you are referencing to contains Text values. The way you construct the criteria depends on the type of the data held in the criteria field)

The criteria argument is written as follows:
(text highlighted in red is the part where you will specify the criteria)

This means that the entire criteria argument will be passed as string to the MS Access data engine. Because criteria value is going to be of text data type, it will be included within single quotes‘’ as follows:

Now, pay attention now – Here comes the puzzling part.

Before you understand how to construct a criteria while referencing directly to a field, let us see how we construct it when we directly provide a text value.

1. Provide a direct value :
If the criteriavalue that you are going to specify is a direct text like CityA, then you would directly specify that text within the single quotes as:
“Cfield =‘CityA’”
It would mean that DSUM would perform addition of the field shipping fee for the city ‘CityA’ only. In our example if we specify the city ‘Miami’, then we would get the same total across all cities as follows:

2. Provide a reference to a field :
We want DSUM() to calculate and display the sum corresponding to respective cities. So,we would reference directly to the field as discussed at the beginning of point 3 before.

The criteriavalue itself is going to be a text, hence we would require a string within a string. In other words, the criteria string “Cfield =criteriavalue”will further include another string. That another string would be concatenated as
“&[Ship City]&”

So, you’’ll create the query as follows:

On running the above query, the result is as follows:

Multiple Criteria

You might have noticed that in the query constructed above, there was only one criteria – City :

The multiple criteria can be constructed as follows:

On creating this query in the query grid and after running it, you’ll get the following output :

Summary

So, in this way as explained above, you can create a DSUM function by specifying the criteria as per your need.

A summary of different criteria types is as follows:

I guess you peruse and practice this concept before you move on to the other criterias (like number and date). Please go through this post carefully, try out different examples and share some interesting results/comments with everyone here.

Next Steps : DSUM() with Other Criteria

In the next post, you’ll learn to create a DSUM() with other criterias such as number and text.

17 responses to “DSUM() function –The Art of Writing a Criteria : 1”

1. Pam

Thank you for one of the best explanations of the criteria expressions I have ever seen. You really have explained it in non-technical language. I have always battled with the criteria expressions for dsum and dlookup and this makes it much easier.

• I am glad you liked the post….Stay tuned for more. Btw, sorry for such a late reply 🙂

2. Dorrell Walters

Tremendously pleased with this post, used to be robotic with DSum, now I am much wiser

• Thanks Dorrell. Good to know that you found the post useful. Don’t forget to subscribe to email & RSS feeds – that will help you stay updated with the latest posts.

3. Rich

Thank goodness someone has finally explained this clearly. (“A criteria” might upset the grammar purists, but who cares?)

• Thanks for your feedback, Rich. I am glad you found the post useful. As for “A criteria”, ouch – too lazy to change it now (a good catch though)

4. Rene Hegge

Wow. Best walk trough on Dsum I ever had!
Thanks

5. matthewpetty

Thank you so much for explaining all this. I only discovered DSUM and DCOUNT this week, and now I understand how to use them!

• Hey Matthew, That’s great to know that you found this post useful. Please stay tuned and in the meantime you can check other posts as well. Btw, do you have anything specific in mind that you would like me to cover in this blog? Let me know.

6. ryan

I’m currently using the DSum function in a field in MS Access form but I can’t seem to work out the logical operators involved in the criteria, here’s my formula:
=DSum(“Quarterly_income_tax_year_paid”,”ClientCollection”,”SA_no=” & [Forms]![ClientCollection]![SA_no] And “OR_no<" & [Forms]![ClientCollection]![OR_no])
Can you please help me correct any errors in my formula because it sums up all the values in the "Quarterly_income_tax_year_paid" field instead of following the given criteria. Any help would be much appreciated. Thanks in advance.

7. Riv03

Thank you for this brilliantly clear and concise explanation, i wish i could have gotten to this sooner though- unfortunately your blog was blocked from our work site… go figure. However i managed to get my dsum to work. The trouble is i cant seem to run it straight from my query (140 records) it just keeps running and eventually access is “not responding” and i have to force shut it. Now I had to make the query a table and then create a query calling that table and calculating dsum. However, now i face a bigger problem ito updating & appending the table. Do you have any insight how to get this dsum to run in my query? It would save me a ton of work.. Thank you.

8. Mel3

A tremendous help. This is the only place on the web where I found a clear explanation of single and double quote usage in the construction of a DSum query. Thank you.