# Sum data by using a query

Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.

This article explains how to use a type of function called an aggregate function to sum the data in a query result set. This article also briefly explains how to use other aggregate functions, such as COUNT and AVG to count or average the values in a result set. In addition, this article explains how to use the Total Row, a feature in Access that you use to sum data without having to alter the design of your queries.

## What do you want to do?

Understand ways to sum data

Prepare some sample data

Sum data by using a Total row

Calculate grand totals by using a query

Calculate group totals by using a totals query

Sum data across multiple groups by using a crosstab query

Aggregate function reference

## Understand ways to sum data

You can sum a column of numbers in a query by using a type of function called an aggregate function. Aggregate functions conduct a calculation on a column of data and return a single value. Access supplies a collection of aggregate functions, including SumCountAvg (for computing averages), Min and Max. You sum data by adding the Sum function to your query, you count data by using the Count function, and so on.

In addition, Access offers numerous ways to add Sum and other aggregate functions to a query. You can:

• Open your query in Datasheet View and add a Total row. The Total Row, a feature in Access, enables you to employ an aggregate function in one or more columns of a query result set without having to change the design of your query.
• Design a totals query. A totals query calculates subtotals across groups of records; a Total row calculates grand totals for one or more columns (fields) of data. For example, if you want to subtotal all sales by city or by quarter, you use a totals query to group your records by the desired category and you then sum the sales figures.
• Formulate a crosstab query. A crosstab query is a unique kind of query that presents its results in a grid that mimics an Excel worksheet. Crosstab queries summarise your values and then group them by two sets of facts — one set down the side (row headings), and the other across the top (column headings). For example, you can use a crosstab query to display sales totals for each city for the past three years, as the following table shows:

Note: The how-to sections in this document emphasise using the Sum function, but remember that you can use other aggregate functions in your Total rows and queries. For more information about using the other aggregate functions, see the section Aggregate function reference later in this article.

For more information about ways to use the other aggregate functions, see the article Display column totals in a datasheet.

The steps in the following sections explain how to add a Total row, use a totals query to sum data across groups, and how to use a crosstab query that subtotals data across groups and time intervals. As you proceed, remember that many of the aggregate functions work only on data in fields set to a specific data type. For example, the SUM function works only with fields set to the Number, Decimal, or Currency data types. For more information about the data types that each function requires, see the section Aggregate function reference, later in this article.

For general information about data types, see the article Modify or change the data type set for a field.

Top of Page

## Prepare some sample data

The how-to sections in this article provide tables of sample data. The how-to steps use the sample tables in order to help you understand how the aggregate functions work. If you prefer, you can optionally add the sample tables into a new or existing database.

Access provides several ways to add these sample tables to a database. You can enter the data manually, you can copy each table into a spreadsheet programme such as Excel and then import the worksheets into Access, or you can paste the data into a text editor such as Notepad and import the data from the resulting text files.

The steps in this section explain how to enter data manually in a blank datasheet, and how to copy the sample tables to a spreadsheet programme, and then import those tables into Access. For more information about creating and importing text data, see the article Import or link to data in a text file.

The how-to steps in this article use the following tables. Use these tables to create your sample data:

The Categories table:

The Products table:

The Orders table:

The Order Details table:

Note: Remember that in a typical database an order details table will contain only a Product ID field, not a Product Name field. The sample table uses a Product Name field to make the data easier to read.

### Enter the sample data manually

1. On the Create tab, in the Tables group,pick Table.Access adds a new, blank table to your database.

Note: You do not need to follow this step if you open a new, blank database, but you will need to follow it whenever you need to add a table to the database.

1. Double-click the first cell in the header row and specify the name of the field in the sample table. By default, Access symbolises blank fields in the header row with the text Add New Field, like so:
2. Select the arrow keys to move to the next blank header cell , and enter the second field name (you can also press TAB or double-click the new cell). Repeat this step until you type every field name.
3. Type the data in the sample table. As you input the data, Access deduces a data type for each field. If you are new to relational databases, you should format a specific data type, such as Number, Text, or Date/Time, for all the fields in your tables. Setting the data type assists with confidently capturing accurate data and even aids with precluding errors, like using a telephone number in a calculation. For these sample tables, you should allow Access to decipher the data type.
4. After you finish entering the data, press Save. Keyboard shortcut = Press CTRL+S. The Save As dialogue box emerges.
5. In the Table Name box, state the name of the sample table, and then choose OK. You apply the name of each sample table because the queries in the how-to sections use those names.
6. Repeat these steps until you design every sample table mentioned at the beginning of this section.

If you do not want to enter the data manually, follow the next steps to copy the data to a spreadsheet file, and then import the data from the spreadsheet file into Access.

### Create the sample worksheets

1. Begin your spreadsheet programme and produce a new, blank file. If you use Excel, it creates a new, blank workbook by default.
2. Copy the first sample table offered above and paste it into the first worksheet, starting at the first cell.
3. Utilising the technique supplied by your spreadsheet programme, rename the worksheet. Entitle the worksheet with the exact name as the sample table. For example, if the sample table is named Categories, give your worksheet the same name.
4. Repeat steps 2 and 3, copying each sample table to a blank worksheet and renaming the worksheet.

1. Save the workbook to an apt location on your computer or your network, and progress to the next set of steps.

### Create database tables from the worksheets

1. On the External Data tab, in the Import group, pick Excel. -or- Choose More, and then pick a spreadsheet programme from the list. The Get External Data – Programme Name Spreadsheet dialogue box emerges.
2. Select Browse, access the spreadsheet file that you created in the previous steps, and then choose OK. The Import Spreadsheet Wizard starts.
3. Automatically, the wizard chooses the first worksheet in the workbook (the Customers worksheet, if you followed the steps in the previous section), and data from the worksheet displays in the lower section of the wizard page. Press Next.
4. On the next page of the wizard, pick First row contains column headings, and then choose Next.
5. Optionally, on the next page, employ the text boxes and lists under Field Options to alter field names and data types or to skip fields from the import operation. Otherwise, select Next.
6. Leave the Let Access add primary key option chosen, and pick Next.
7. Intuitively, Access relays the name of the worksheet to your new table. Approve the name or type another name, and then press Finish.
8. Repeat steps 1 until 7 to the stage where you have formed a table from each worksheet in the workbook.

### Rename the primary key fields

Note: When you imported the worksheets, Access automatically added a primary key column to each table and, by default, Access named that column “ID” and set it to the AutoNumber data type. The steps in this explain how to rename each primary key field. Doing so helps to clearly identify all the fields in a query.

1. In the Navigation Pane, right-click each of the tables that you created in the previous steps and pick Design View.
2. For each table, find the primary key field. By default, Access names each field ID.
3. In the Field Name column for each primary key field, add the name of the table. For example, you would rename the ID field for the Categories table to “Category ID” and the field for the Orders table to “Order ID.” For the Order Details table, rename the field to “Detail ID.” For the Products table, rename the field to “Product ID.”
4. Confirm your changes by clicking Save.

Whenever the sample tables appear in this article, they include the primary key field, and the field is renamed as described by using the preceding steps.

Top of Page

## Sum data by using a Total row

You can add a Total row to a query by opening your query in Datasheet View, adding the row, and then selecting the aggregate function that you want to use, such as SumMinMax, or Avg. The steps in this section explain how to create a basic select query and add a Total row. You do not need to use the sample tables described in the previous section.

### Create a basic select query

1. On the Create tab, in the Other group, pick Query Design.
2. In the Show Tables dialogue box, double-click the table or tables that you want to use in your query, and then choose Close. The picked table or tables appear as windows in the upper section of the query designer.
3. Double-click the table fields that you want to use in your query. You can incorporate fields with descriptive data, such as names and descriptions, but you must include a field that contains numeric or currency data. Each field materialises in a cell in the design grid.
4. Select Run  to run the query. The query result set launches in Datasheet View.
5. Optionally, switch to Design View and adjust your query. To do so, right-click the document tab for the query and pick Design View. You can then modify the query, as needed, by appending or deleting table fields. To remove a field, choose the column in the design grid and press DELETE.

1. Ensure that your query is open in Datasheet View. To do so, right-click the document tab for the query and press Datasheet View. -or- In the Navigation Pane, double-click the query. This performs the query and loads the results into a datasheet.
2. On the Home tab, in the Records group, select Totals. A new Total row displays in your datasheet.
3. In the Total row, select the cell in the field that you want to sum, and then press Sum from the list.

### Hide a Total row

• On the Home tab, in the Records group, choose Totals.

For more information about using a Total row, see the article Display column totals in a datasheet.

Top of Page

## Calculate grand totals by using a query

A grand total is the sum of all the values in a column. You can calculate several types of grand totals, including:

• A simple grand total that sums the values in a single column. For example, you can calculate total shipping costs.
• A calculated grand total that sums the values in more than one column. For example, you can calculate total sales by multiplying the cost of several items by the number of item ordered, and then totaling the resulting values.
• A grand total that excludes some records. For example, you can calculate the total sales only for last Friday.

The steps in the following sections explain how to create each type of grand total. The steps use the Orders and Order Details tables.

The Orders table

The Order Details table

### Calculate a simple grand total

1. On the Create tab, in the Other group, pick Query Design.
2. In the Show Table dialogue box, double-click your desired table to use in your query, and then press Close. If you apply the sample data, double-click the Orders table. The table displays in a window in the upper section of the query designer.
3. Double-click your target field to sum. Check that the field is set to either the Number or Currency data type. If you continue to sum values in non-numeric fields, such as a Text field, Access launches the Data type mismatch in criteria expression error message once you try to run the query. If you utilise the sample data, double-click the Shipping Fee column. You can append further 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.
4. On the Design tab, in the Show/Hide group, select TotalsThe Total row emerges in the design grid and Group By comes up in the cell in the Shipping Fee column.
5. Update the value in the cell in the Total row to Sum.
6. Press Run  to run the query and show the results in Datasheet View.

Tip: Note that Access appends “SumOf” to the beginning of the name of the field that you sum. To change the column heading to something more meaningful, such as Total Shipping, switch back to Design View, and click in the Field row of the Shipping Fee column in the design grid. Place the cursor next to Shipping Fee and type the words Total Shipping, followed by a colon, like so: Total Shipping: Shipping Fee.

1. Optionally, save the query and close it.

### Calculate a grand total that excludes some records

1. On the Create tab, in the Other group, choose Query Design.
2. In the Show Table dialogue box, double-click the Order table and Order Details table, and then select Close to close the dialogue box.
3. Insert the Order Date field from the Orders table to the first column in the query design grid.
4. In the Criteria row of the first column, state Date() -1. That expression excludes the current day’s records from the calculated total.
5. Next, design the column that calculates the sales amount for each transaction. Enter the following expression in the Field row of the second column in the grid: Total Sales Value: (1-[Order Details].[Discount]/100)*([Order Details].[Unit Price]*[Order Details].[Quantity]) Ensure your expression references fields set to the Number or Currency data types. If your expression relates to fields set to other data types, Access presents the message Data type mismatch in criteria expression anytime you try to run the query.
6. On the Design tab, in the Show/Hide group, choose Totals. The Total row displays in the design grid and Group By emerges in the first and second columns.
7. In the second column, amend the value in the cell of the Total row to Sum. The Sum function creates the individual sales figures.
8. Pick Run  to run the query and present the results in Datasheet View.
9. Save the query as Daily Sales.

Note: The next time that 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 appears enclosed inside the Sum function, and the Total row displays Expression instead of Sum.

1. For example, if you use the sample data and create the query (as shown in the previous steps), you see: Total Sales Value: Sum((1-[Order Details].Discount/100)*([Order Details].Unitprice*[Order Details].Quantity))

Top of Page

## Calculate group totals by using a totals query

The steps in this section explain how to create a totals query that calculates subtotals across groups of data. As you proceed, remember that by default, a totals query can include only the field or fields that contain your group data, such as a “categories” field, and the field that contains the data that you want to sum, such as a “sales” field. Totals queries cannot include other fields that describe the items in a category. If you want to see that descriptive data, you can create a second select query that combines the fields in your totals query with the additional data fields.

The steps in this section explain how to create a totals and select queries needed to identify the total sales for each product. The steps assume the use of these sample tables:

The Products table

The Order Details table

The following steps assume a one-to-many relationship between the Product ID fields in the Orders table and Order Details table, with the Orders table on the “one” side of the relationship.

### Create the totals query

1. On the Create tab, in the Other group, pick Query Design.
2. In the Show Table dialogue box, select the tables that you want to work with, pick Add, and then choose Close after you’re done inserting the tables. -or- Double-click your sought tables to apply, and then select Close. Each table emerges as a window in the upper section of the query designer. If you use the sample tables listed previously, you add the Products and Order Details tables.
3. Double-click your desired table fields refer to in your query. As a rule, you insert only the group field and the value field to the query. However, you can employ a calculation rather than a value field — the next steps explain how to do so.

a. Create the Category field from the Products table to the design grid.

b. Formulate the column that calculates the sales amount for each transaction by typing the following expression in the second column in the grid: Total Sales Value: (1-[Order Details].[Discount]/100)*([Order Details].[Unit Price]*[Order Details].[Quantity]) Check that the fields that you reference in the expression are of the Number or Currency data types. If you reference fields of other data types, Access displays the error message Data type mismatch in criteria expression when you try to switch to Datasheet View.

c. On the Design tab, in the Show/Hide group, pick Totals. The Total row displays in the design grid, and in that row, Group By emerges in the first and second columns.

d. In the second column, edit the value in the Total row to Sum. The Sum function appends the individual sales figures.

e. Select Run  to run the query and display the results in Datasheet View.

f. Leave the query open for use in the next section.Use criteria with a totals queryThe query that you created in the previous section includes all the records in the underlying tables. It does not exclude any order when calculating the totals, and it displays the totals for all categories.If you need to exclude some records, you can add criteria to the query. For example, you can ignore transactions that are less than \$100 or calculate totals for only some of your product categories. The steps in this section explain how to use three types of criteria:

g. Criteria that ignore certain groups when calculating totals.    For example, you will calculate totals for just the Video Games, Art and Framing, and Sports categories.

h. Criteria that hide certain totals after calculating them.    For example, you can display only the totals greater than \$150,000.

i. Criteria that exclude individual records from being included in the total.    For example, you can exclude individual sales transactions when the value (Unit Price * Quantity) drops below \$100.The following steps explain how to add the criteria one by one and see the impact on the query result.Add criteria to the query

j. Open the query from the previous section in Design View. To do so, right-click the document tab for the query and click Design View.-or-In the Navigation Pane, right-click the query and click Design View.

k. In the Criteria row of the Category ID column, type =Dolls Or Sports or Art and Framing.

l. Click Run  to run the query and display the results in Datasheet View.

m. Toggle back to Design View and, in the Criteria row of the Total Sales Value column, enter >100.

n. Run the query to see the results, and then switch back to Design View.

o. Now append the criteria to skip separate sales transactions that are less than \$100. To do this, you must create 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.

p. Duplicate the expression from the second column to the third column.

q. In the Total row for the new column, pick Where and, in the Criteria row, type >20.

r. Perform the query to view the results, and then save the query.

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*[Orde r Details].Quantity))

You will also notice a fourth column. This column is an imitation of the second column, but the criteria that you set out in the second column genuinely presents itself as part of the new column.

Top of Page

## Sum data across multiple groups by using a crosstab query

A crosstab query is a remarkable kind of query that showcases its results in a grid akin to an Excel worksheet. Crosstab queries summarise your values and then organise them by two ranges of facts — one set down the side (a set of row headers), and the other across the top (a set of column headers). This figure denotes some of the result set for sample crosstab query:

As you go along, keep in mind that a crosstab query intermittently fills each of the fields in the result set since the tables that you use in the query often include values for selective data points as opposed to every one.

Once you design a crosstab query, you usually encompass data from multiple tables, and you constantly enclose three types of data: the data used as row headings, the data used as column headings, and the values that you want to sum or otherwise compute.

The steps in this section assume the following tables:

The Orders table

The Order Details table

The following steps delineate how to generate a crosstab query that categorises total sales by city. The query applies two expressions to return a formatted date and a sales total.

### Create a crosstab query

1. On the Create tab, in the Other group, pick Query Design.
2. In the Show Tables dialogue box, double-click your desired tables to utilise in your query, and then select Close. Every table manifests as a window in the upper section of the query designer. If you employ the sample tables, double-click the Orders table and the Order Details table.
3. Double-click your sought fields to use in your query. Every field name displays in the a blank cell in the Field row of the design grid. If you apply the sample tables, append the Ship City and Ship Date fields from the Orders table.
4. In the next blank cell in the Field row, copy and paste or write the following expression:  Total Sales: Sum(CCur([Order Details].[Unit Price]*[Quantity]*(1-[Discount])/100)*100)
5. On the Design tab, in the Query Type group, select Crosstab. The Total row and the Crosstab row emerge in the design grid.
6. Choose the cell in the Total row in the City field and press Group By. Repeat this step for the Ship Date field. Edit the value in the Total cell of the Total Sales field to Expression.
7. In the Crosstab row, define the cell in the City field as Row Heading, set the Ship Date field to Column Heading, and set the Total Sales field to Value.
8. On the Design tab, in the Results group, pick Run. The query results are presented in Datasheet View.

Top of Page

## Aggregate function reference

This table displays and describes the aggregate functions that Access offers in the Total row and in queries. Remember that Access provides additional aggregate functions for queries than it does for the Total row. Also, if you work with an Access project (an Access front end connected to a Microsoft SQL Server database), you can apply the broader set of aggregate functions that SQL Server gives. For more information about that set of functions, see Microsoft SQL Server Books Online.

Top of Page