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.

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

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 :

DSUM() function –The Art of Writing a Criteria

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.

DSUM() function –The Art of Writing a Criteria

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

DSUM() function –The Art of Writing a Criteria

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.

DSUM() function –The Art of Writing a Criteria

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

Remember : The text/number/date can either be specified directly or it can be specified by referencing it to a column containing the required  text/number/date  values. This will get clearer as we go through more examples below.

 

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:

DSUM() function –The Art of Writing a Criteria

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)
                                                    DSUM() function –The Art of Writing a Criteria

    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:DSUM() function –The Art of Writing a Criteria
(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:

DSUM() function –The Art of Writing a Criteria

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:
                                        DSUM() function –The Art of Writing a Criteria
    DSUM() function –The Art of Writing a Criteria
  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]&”

DSUM() function –The Art of Writing a Criteria

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

DSUM() function –The Art of Writing a Criteria

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

DSUM() function –The Art of Writing a Criteria

Note: Different data types will require different delimiters. Delimiters tell Access that the value that it is going to encounter next is a text value or a date value or a numerical value. So, for text value the delimiter is quotes, for date value the delimiter is # whereas for numerical value there is no delimiter required.

  

Multiple Criteria

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

DSUM() function –The Art of Writing a Criteria 

The multiple criteria can be constructed as follows:

DSUM() function –The Art of Writing a Criteria

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

DSUM() function –The Art of Writing a Criteria

DSUM() function –The Art of Writing a Criteria

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:

DSUM() function –The Art of Writing a Criteria

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.

Advertisements

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

  1. 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.

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

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

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

  5. 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. 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. 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. 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.

  9. Hello everybody, i’ve read the notes above and i’m trying to build a Dsum but it just wont work for me. This is the expression can anybody please straighten it up for me? PLevel: DSum(“[Quantity]”,”[tblPurchases]”,”[BrandNamefk]=” & [BrandNamefk] & “” And “[ProductTypefk]=” & [productTypefk] & “”). I have a purchases table which contains some two lookup fields, Brand Name and Product Type. Now i want to build a query to retrieve the sum of total purchases for Brand Name and the corresponding Product Types. i have a one to many relationship between the Brand table and the product type table. Thanks in advance.

  10. Thanks so much for this guide, I would never have figured out the syntax on my own!

  11. Pingback: DSUM() function –The Art of Writing a Criteria : 1 | | MS Access

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s