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:
Construct a DSUM() function with no Criteria
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 :
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:
Shipping fee Total
The query will be constructed as follows:
As can be seen from the screenshot above:
You drag the field [Ship City] in the query grid.
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.
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.
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:
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:
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
So, you’’ll create the query as follows:
On running the above query, the result is as follows:
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.
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 :
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.
- DSUM() function –The Art of Writing a Criteria : 2
- DSUM() function –The Art of Writing a Criteria : 3