Summing data by using a query

Applies to
Microsoft Office Access 2003

This topic describes how to sum data by using a query. If you want to sum data by creating a report, see the topic Summing in reports.

Let's start by looking at the different ways in which you can sum data by using a query.

In this article

Calculate a grand total
A grand total is the sum of all the values in a column.

Calculate group totals
You can group records on one or more fields and then sum the values in a field or calculated column for each group of records.

Calculate the average for a table or groups of records
You can calculate an average of all values in a field or calculated column, or calculate averages for groups of records.

Calculate a running sum
A running sum is calculated for each record by accumulating (summing) the values in a specific column, starting from the first record and ending with the current record.

Sum two or more columns in a new column
You can sum the values in two or more columns for each record and show the resulting values in a new column.

pencil and calculator

Do you often work with numbers? Do you spend a lot of time crunching numbers by using a calculator, or worse, paper and pen? If your answer is yes, this article is for you.

Whether you run a small business or work for a big corporation, you know this already — the numbers change, but how you summarize the figures does not change very much. For example, suppose the first thing that you do each day is track down the previous day's orders and summarize them for your supervisor. The order details change each day, but the steps that you take to summarize the details change much less frequently.

If you find yourself doing the same calculations every day or several times a month or year, you should delegate the chore to your database. An Access database, in addition to storing and displaying data in various formats, can perform a wide variety of calculations. This not only helps save you time and effort but also helps minimize the chances of human error and improve the quality of the calculations.

So where do you start? First, decide whether you need to create a query or a report to display the calculated results.

Query and report

Consider the type of information that your audience is looking for when you choose between a query and a report.

  • Who is looking for this information?    If it is for your own use, a query will do. If you need to make the figures available to your team, management, or investors, you should think about creating a report.
  • What is the audience looking for?    If viewing the calculations as a set of rows and columns in Datasheet view is sufficient, go ahead and use a query. If it is necessary to include the individual figures along with the summaries, or if the data needs to be grouped or supplemented with headers, footers, and charts, use a report.

 Note   The examples in this topic are from the Northwind.mdb sample database. To open the database, on the Help menu, point to Sample Databases and click Northwind Sample Database. If the Security Warning dialog box appears, click Open.

To learn how to summarize data by finding the records with the smallest or largest values, see the topic Find the records with top or bottom values in a field.

If you want to know how to summarize or count duplicate records in a table, see the section Count or summarize duplicate recordsLink in the article "Find, eliminate, or hide duplicate records in Access."

Top of Page Top of Page

Calculate a grand total

A grand total is the sum of all the values in a column. For example, you can add the Freight column in the Orders table to calculate the total freight that has been incurred to date.

Total Freight
$64,942.69

You can also calculate a grand total of calculated values. For example, the sales figure is not actually stored in the Northwind.mdb database because it is a calculated value. It can be calculated for each order detail record by multiplying the values in the UnitPrice and Quantity fields and then subtracting the discount amount from this value by using the discount percentage that is stored in the Discount field.

Total Sales
$1,353,571.93

Calculating the grand total of calculated values is not very different from calculating a grand total of a field in a table.

There are also situations where you might want to exclude certain records when you calculate a grand total. For example, you might want to calculate the total of just the sales transactions that took place during the current year or month or on a specific day, and exclude the sales data that pertains to other time periods. The following example shows the result of excluding the sales transactions that took place on all days other than 25-Feb-1997.

Date Total Sales
25-Feb-1997 $2,239.02

To calculate totals that are based on some of the records in a table, you specify the criteria when you create the query.

Step-by-step instructions for calculating a grand total

Start by creating a query to sum the values in the Freight field. Then create another query to calculate the grand total of the calculated sales values.

In both cases, you create a totals query. A totals query shows the summary data instead of the individual records. It allows you to specify how you want to sum one or more numeric columns and automatically does the calculations for you.

  1. In the Database window, under Objects, click Queries, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design view, and then click OK.
  3. In the Show Table dialog box, double-click the Orders table, and then click Close to close the dialog box.
  4. Add the Freight field to the first column. Make sure the field that contains the values that you want to sum has either a Number or a Currency data type. If you try to sum values in nonnumeric fields, such as Text, you receive the error message "Data type mismatch in criteria expression" when you try to switch to Datasheet view.

 Note   Add additional numeric fields to the grid if you want to calculate grand totals for those fields. A totals query can calculate grand totals for more than one column.

  1. Click Totals Button image on the Query Design toolbar. This adds the Total row to the grid — this row displays Group By in the Freight column.
  2. Change the value in the Total row to Sum. Sum adds the values in the Freight column of all the records in the Orders table.

Freight column in design grid with Total row set to Sum

  1. Click View Button image to switch to Datasheet view.
SumOfFreight
$64,942.69
  1.  Tip   To change the column heading to something more meaningful, such as Total Freight, switch back to Design view, and in the Field row of the Freight column in the design grid, type Total Freight: Freight.

  2. Save the query as Total Freight and close it.

Now create another totals query that will calculate the previous day's total sales. This query includes a calculated column to calculate the sales value and also contains the criteria for including only those records that correspond to the previous day's orders.

  1. In the Database window, under Objects, click Queries and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design view, and then click OK.
  3. In the Show Table dialog box, double-click the Order and Order Details tables, and then click Close to close the dialog box.

The Orders table contains the OrderDate field that is necessary to identify the previous day's orders. The Order Details table contains the Quantity, UnitPrice, and Discount fields that are necessary to calculate the sales amount.

  1. Add the OrderDate field from the Orders table to the first column in the query design grid.
  2. In the Criteria row of the first column, type the expression that is used to restrict the records that are included in the calculation of totals. To include only the previous day's orders, type Date() -1 in the Criteria row.

OrderDate column in design grid with expression to include previous day's orders

  1. Next, you need to create the column that is used to calculate the sales amount for each transaction. Type the following expression in the Field row of the second column in the grid:

Total Sales Value: (1-[Order Details].[Discount]/100)*([Order Details].[UnitPrice]*[Order Details].[Quantity])

Make sure the fields that you reference in the expression are of data type Number or Currency. If you reference fields of other data types, you receive the error message "Data type mismatch in criteria expression" when you try to switch to Datasheet view.

  1. Click Totals Button image on the Query Design toolbar. This adds the Total row to the grid — this row displays Group By in the first and second columns.
  2. In the second column, change the value in the Total row to Sum. The Sum function adds the individual sales figures.

OrderDate and Total Sales Value columns in design grid

  1. Click View Button image to switch to Datasheet view. If today's date is 2/26/1997, you see what is shown in the following illustration.
Order Date Total Sales Value
25-Feb-1997 2239.0159999609
  1. The value in the Total Sales Value column is not formatted as currency. Now, format the calculated column. Click View to switch back to Design view.
  2. Click anywhere inside the Total Sales Value column, and then click Properties Button image on the toolbar.
  3. Set the Format property to Currency.
  4. Click View to switch back to Datasheet view.
Order Date Total Sales Value
25-Feb-1997 $2239.02
  1. Save the query as Daily Sales.

 Note   The next time you open the query in Design view, you might notice a slight change in the values specified in the Field and Total rows of the Total Sales Value column. The expression will appear enclosed inside the Sum function, and the Total row will display Expression instead of Sum.

Total Sales Value: Sum((1-[Order Details].Discount/100)*([Order Details].Unitprice*[Order Details].Quantity))

Top of Page Top of Page

Calculate group totals

You can group records on one or more fields and then sum the values in a field or calculated column for each group of records. The following illustration shows the sales figures for each product category.

Category Total Sales Value
Beverages $286,340.36
Condiments $113,618.27
Confections $177,001.68
Dairy Products $251,162.27
Grains/Cereals $100,676.98
Meat/Poultry $178,037.14
Produce $105,215.76
Seafood $141,519.48

You can also use criteria to restrict the groups for which you calculate totals, to limit certain records from being included in the group, and more.

Step-by-step instructions for calculating a group total

Create a totals query to calculate the total sales for each product category.

  1. In the Database window, under Objects, click Queries and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design view and then click OK.
  3. In the Show Table dialog box, double-click the Order Details and Products tables, and then click Close to close the dialog box.

The Order Details table contains the Quantity, UnitPrice, and Discount fields that are necessary to calculate the sales amount. The Products table contains the category information for each product that was sold.

  1. Add the CategoryID field from the Products table to the first column.
  2. Next, create the column that is used to calculate the sales amount for each transaction. Type the following expression in the second column in the grid:

Total Sales Value: (1-[Order Details].[Discount]/100)*([Order Details].[UnitPrice]*[Order Details].[Quantity])

Make sure the fields that you reference in the expression are of data type Number or Currency. If you reference fields of other data types, you receive the error message "Data type mismatch in criteria expression" when you try to switch to Datasheet view.

  1. Click Totals Button image on the Query Design toolbar. This adds the Total row to the grid — this row displays Group By in the first and second columns.
  2. In the second column, change the value in the Total row to Sum. Sum adds the individual sales figures.

CategoryID and Total Sales Value columns in design grid

  1. The values in a calculated column are not formatted as currency unless you explicitly set the Format property of the column in query Design view. Click anywhere inside the Total Sales Value column, and then click Properties Button image on the toolbar.
  2. Set the Format property to Currency.
  3. Click View Button image to switch to Datasheet view.
Category Total Sales Value
Beverages $286,340.36
Condiments $113,618.27
Confections $177,001.68
Dairy Products $251,162.27
Grains/Cereals $100,676.98
Meat/Poultry $178,037.14
Produce $105,215.76
Seafood $141,519.48

The query includes all the records that are in the underlying tables. It does not exclude any order when calculating the totals, and it displays the totals for all categories.

What if you want to exclude some records? For example, what if you want to ignore transactions that are less than $100 or calculate totals for only some of the categories? Or you want to display a group total only if the total is greater than $150,000? It is easy to exclude records when you calculate group totals.

Now, you modify the query that you just created to include three types of criteria:

  • Criteria that ignore certain groups when calculating totals. For example, you will calculate totals only for Beverages, Condiments, and Confections.
  • Criteria that hide certain totals after calculating them. For example, you want to display the totals only if they are greater than $150,000.
  • Criteria that exclude individual records from being included in the total. For example, add a criteria to exclude individual sales transactions if the value (UnitPrice * Quantity) is less than $100.

You will add the criteria one by one and see the impact on the query result.

  1. Click View to switch to Design view.
  2. In the Criteria row of the CategoryID column, type =1 Or 2 Or 3. IDs 1, 2, and 3 belong to categories Beverages, Condiments, and Confections, respectively.
  3. Click View to see the result of adding this criteria. In Datasheet view, you see totals only for the three groups.
Category Total Sales Value
Beverages $286,340.36
Condiments $113,618.27
Confections $177,001.68
  1. Click View to switch back to Design view.
  2. In the Criteria row of the Total Sales Value column, type >150000.
  3. Click View to see the result of adding this criteria. You see only the totals for Beverages and Confections. The total for Condiments is less than $150,000, so it is not displayed in the view.
Category Total Sales Value
Beverages $286,340.36
Confections $177,001.68
  1. Click View to switch back to Design view.
  2. Now add the criteria to exclude individual sales transactions that are less than $100. To do this, you need to add another column.

 Note   You cannot specify the third criteria in the Total Sales Value column. Any criteria that you specify in this column applies to the total value, not to the individual values.

  1. Copy the expression from the second column to the third column. In the Total row, select Where. In the Criteria row, type >100.

3 criteria to restrict results of query

  1. Click View Button image to see the result of all three criteria specified in the query.
Category Total Sales Value
Beverages $281,046.95
Confections $173,802.40
  1. Save the query as Sales by Category.

 Note   The next time you open the query in Design view, you might notice slight changes in the design grid. In the second column, the expression in the Field row will appear enclosed inside the Sum function, and the value in the Total row displays Expression instead of Sum.

Total Sales Value: Sum((1-[Order Details].Discount/100)*([Order Details].Unitprice*[Order Details].Quantity))

You will also see a fourth column. This column is a copy of the second column, but the criteria that you specified in the second column actually appears as part of the new column.

Top of Page Top of Page

Calculate the average for a table or groups of records

Calculating an average of the values in a column or for groups of records is very similar to calculating totals. The only difference is this — to calculate averages, you set the value in the Total row to Avg, instead of Sum.

Average freight paid for an order
$78.24

Step-by-step instructions for calculating an average

  1. Open any of the queries that you created earlier in Design view.
  2. In the expression in the Field row, change Sum to Avg. The Avg function calculates an average of the values in the specified records. The following illustration shows the Daily Sales query in Design view. The Sum function has been replaced with the Avg function in the Field row of the second column.

Daily Sales query in Design view

 Note   If you are creating a new query instead of modifying an existing one, set the value in the Total row to Avg instead of Sum.

  1. Click View Button image to switch to Datasheet view to see the result.

The following illustration shows the result of modifying the Total Freight query to calculate the average freight paid for an order.

Average Freight
$78.24

 Note   Remember to change the column name from Total Freight to Average Freight to avoid confusion.

If you modified the Daily Sales query to calculate the average value of the previous day's sales transactions, you will see the following.

Order Date Average Sales Value
25-Feb-1997 $746.34

If you modify the Sales by Category query, remember to change the criteria in the second column. The average for each group will be much lower than $150,000, so change the criteria to, say >600. Otherwise, you will not see any data in Datasheet view.

Top of Page Top of Page

Calculate a running sum

A running sum is calculated for each record by accumulating (summing) the values in a specific column, starting from the first record and ending with the current record. For example, the following illustration calculates a running sum of order totals. The second column shows the total value of each order, and the third column shows the running total for each order.

Order ID Order Total Running Sum
10248 $440.00 $440.00
10249 $1,863.40 $2303.40
10250 $1,813.00 $4116.40
10251 $670.80 $4787.20
... ... ...

Step-by-step instructions for calculating a running sum

Now you will create a query that calculates the total value of each order and also calculates a running sum of the value of each order.

Order ID Order Total Running Sum
10248 $440.00 $440.00
10249 $1,863.40 $2303.40
10250 $1,813.00 $4116.40
10251 $670.80 $4787.20
... ... ...

Note that this query is not a totals query. It uses the DSum function in an expression to calculate the running sum.

  1. In the Database window, under Objects, click Queries and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design view and then click OK.
  3. In the Show Table dialog box, double-click the Order Details table, and then click Close to close the dialog box.
  4. Add the OrderID field to the first column.
  5. Next, add the expression to calculate the value of each order. Type the following expression in the Field row:

Order Value: (DSum("[Order Details].[UnitPrice]*[Order Details].[Quantity]","Order Details","[OrderID] = " & [Order Details].[OrderID]))

The DSum function in this expression totals the value of the expression (UnitPrice * Quantity) for each record in the Order Details table, after first grouping them by OrderID.

  1. Next, add the column that is used for displaying the running sum. Type the following expression in the Field row:

Running Total: (DSum("[Order Details].[UnitPrice]*[Order Details].[Quantity]","Order Details","[OrderID] <= " & [Order Details].[OrderID]))

OrderID and calculated columns in design grid

The DSum function in this expression totals the value of the expression (UnitPrice * Quantity) for each record in the Order Details table, after first grouping all transactions where the OrderID is less than or equal to the ID of the current record. As the calculation progresses from one order ID to the next, the running sum keeps increasing.

  1. Calculated columns will not show currency values unless you explicitly format them as such. Unlike a totals query, you cannot rely on the Format property of an underlying field — in this case, the UnitPrice field in the Order Details table — to change the format of the values. When you use the DSum function, you need to also use the Format function to format the values that are returned by the DSum function. Enclose the expression in the second column inside the Format function, as shown here:

Format(existing expression, "Currency")

For example, the Field row of the Order Total column contains the following expression:

Order Value: Format(DSum("[Order Details].[UnitPrice]*[Order Details].[Quantity]","Order Details","[OrderID] = " & [Order Details].[OrderID]), "Currency")

  1. Repeat the previous step for the third column.
  2. Click View Button image to switch to Datasheet view. This might take a few seconds, depending on the number of records in the table.
Order ID Order Total Running Sum
10248 $440.00 $440.00
10249 $1,863.40 $2303.40
10250 $1,813.00 $4116.40
10251 $670.80 $4787.20
... ... ...
  1. Save the query as Running Sum, and close it.

Top of Page Top of Page

Sum two or more columns in a new column

You can sum the values in two or more columns for each record and show the resulting values in a new column. A column that shows the result of a calculation is called a calculated column.

Product Name Units In Stock Units On Order Total Units
Chai 39 0 39
Chang 17 40 57
Aniseed Syrup 13 70 83
... ... ...
... ... ...

Step-by-step instructions for summing two or more columns in a new column

Suppose you need to review the inventory of products. You want to see not only the number of units on hand, but also the number of units that were ordered.

Product Name Units In Stock Units On Order Total Units
Chai 39 0 39
Chang 17 40 57
Aniseed Syrup 13 70 83
... ... ...
... ... ...

To sum the values in two or more columns, you need to create a calculated column. The calculated column will use an expression that uses the addition operator (+) to add the column values for each row.

Now, you will create a query to calculate the Total Units column.

 Note   This query is not a totals query. A totals query sums the values in a column but cannot be used to sum across the columns to produce a total for each record.

  1. In the Database window, under Objects, click Queries and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design view and then click OK.
  3. In the Show Table dialog box, double-click the Products table and then click Close to close the dialog box.
  4. Add the ProductName, UnitsInHand, and UnitsOnOrder fields to the design grid.
  5. Next, add the expression that is used to calculate the total number of units. Type the following expression in the Field row of the fourth column:

Total Units: [UnitsInStock]+[UnitsOnOrder]

3 detail columns and calculated column that summarizes detail columns

The expression sums the values in the two columns and displays the total for each record in the fourth column.

  1. Click View Button image to switch to Datasheet view.
Product Name Units In Stock Units On Order Total Units
Chai 39 0 39
Chang 17 40 57
Aniseed Syru 13 70 83
... ... ...
... ... ...
  1. Save the query as Total Units and close it.

Top of Page Top of Page

 
 
Applies to:
Access 2003