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

In the previous post, you have learnt how DSUM() function works as well as how to specify a text criteria. As mentioned earlier, the criteria could be a text criteria or a number or a date. Depending on the data type of the criteria, there is a slight change in how you construct the criteria part of the DSUM() function in terms of the delimiters to be used as well as the placement of the criteria value.

[Note: If you are new to DSUM() function, it is recommended that you read this post first : DSUM() function – The Art of Writing a Criteria : 1]

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

In this post, you are going to learn more about specifying the number criteria. Let us see how…

Work Scenario

You and your team want to know the total invoice value of all the orders placed. Its a pretty straight forward requirement but you want to use DSUM() function to achieve this.

DSUM function with Number Criteria

In order to run to get a result as required in the work scenario, you’ll be using the table “Order details” and the following field: Order Id, Unit Price, Quantity.

As a recap, the formula for DSUM function is as follows:

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

Also, by now you might be knowing that the criteria can be specified in two ways:

  • Provide a direct value:

Lets say you want to find the total invoice value of an Order ID = 40. To do so, you’ll create the criteria by directly specifying the number as follows:

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

“[Order ID]=40”

Notice that when you want to specify a number criteria there are no delimiters required. You can simply specify the number directly after the = sign.

On constructing and running the query as explained above, you’ll get the following output:

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

And the output is:

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

  • Provide a reference to a field 

You might want a similar output as shown above but you would need it for all the orders in the system. In such a scenario, you will have to construct the criteria by providing a reference to the field holding the order numbers. It will be constructed as follows:

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

“[Order ID]=”&[Order ID]

Note that the Order ID is the field that is holding value of data type – number.
The query would look like as follows:

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

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

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

Summary

In this post, you have seen how to create a DSUM function when the criteria that you are passing is of a number data type.

In the next post, you’ll learn to pass a criteria of data type – Date

About these ads

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