Access for Office 365 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007This 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 dataYou can sum a column of numbers in a query by using a type of function called an aggregate function. Aggregate functions perform a calculation on a column of data and return a single value. Access provides a variety of aggregate functions, including Sum, Count, Avg (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 provides several ways to add Sum and other aggregate functions to a query. You can:.Open your query in Datasheet view and add a Total row.
Slide 3 of 9.
The Total Row, a feature in Access, allows you to use an aggregate function in one or more columns of a query result set without having to change the design of your query.Create 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.Create a crosstab query. A crosstab query is a special type of query that displays its results in a grid that resembles an Excel worksheet. Crosstab queries summarize 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:City05Paris254,567,892Sydney478,076,399Jakarta572,992,571. Note: The how-to sections in this document emphasize 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 later in this article.For more information about ways to use the other aggregate functions, see the article.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, later in this article.For general information about data types, see the article.Prepare some sample dataThe 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 program 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 program, and then import those tables into Access.
For more information about creating and importing text data, see the article.The how-to steps in this article use the following tables. Use these tables to create your sample data:The Categories table:CategoryDollsGames and PuzzlesArt and FramingVideo GamesDVDs and MoviesModels and HobbiesSportsThe Products table:Product NamePriceCategoryProgrammer action figure$12.95DollsFun with C# (A board game for the whole family)$15.85Games and PuzzlesRelational Database Diagram$22.50Art and FramingThe Magical Computer Chip (500 Pieces)$32.65Games and PuzzlesAccess!
The Game!$22.95Games and PuzzlesComputer Geeks and Mythical Creatures$78.50Video GamesExercise for Computer Geeks! 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.Double-click the first cell in the header row and enter the name of the field in the sample table.By default, Access denotes blank fields in the header row with the text Add New Field, like so:.Use the arrow keys to move to the next blank header cell, and type the second field name (you can also press TAB or double-click the new cell). Repeat this step until you enter all field names.Enter the data in the sample table.As you enter the data, Access infers a data type for each field. If you are new to relational databases, you should set a specific data type, such as Number, Text, or Date/Time, for each of the fields in your tables. Setting the data type helps ensure accurate data entry and also helps prevent mistakes, such as using a telephone number in a calculation. Note: You might need to add worksheets to your spreadsheet file.
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.In the Navigation Pane, right-click each of the tables that you created in the previous steps and click Design View.For each table, locate the primary key field. By default, Access names each field ID.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.' .Save your changes.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.Sum data by using a Total rowYou 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 Sum, Min, Max, 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.
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.Optionally, save the query and close it.Calculate a grand total that excludes some records.On the Create tab, in the Other group, click Query Design.In the Show Table dialog box, double-click the Order table and Order Details table, and then click Close to close the dialog box.Add the Order Date field from the Orders table to the first column in the query design grid.In the Criteria row of the first column, type Date -1. That expression excludes the current day's records from the calculated total.Next, create the column that calculates 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.Unit Price.Order Details.Quantity)Make sure your expression references fields set to the Number or Currency data types.
If your expression refers to fields set to other data types, Access displays the message Data type mismatch in criteria expression when you try to run the query.On the Design tab, in the Show/Hide group, click Totals.The Total row appears in the design grid and Group By appears in the first and second columns.In the second column, change the value in the cell of the Total row to Sum. The Sum function adds the individual sales figures.Click Runto run the query and display the results in Datasheet view.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 Expre ssion instead of Sum.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))Calculate group totals by using a totals queryThe 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 tableProduct IDProduct NamePriceCategory1Programmer action figure$12.95Dolls2Fun with C# (A board game for the whole family)$15.85Games and Puzzles3Relational Database Diagram$22.50Art and Framing4The Magical Computer Chip (500 Pieces)$32.65Art and Framing5Access!
The Game!$22.95Games and Puzzles6Computer Geeks and Mythical Creatures$78.50Video Games7Exercise for Computer Geeks! The DVD!$14.88DVDs and Movies8Ultimate Flying Pizza$36.75Sports9External 5.25-inch Floppy Diskette Drive (1/4 Scale)$65.00Models and Hobby10Bureaucrat non-action figure$78.88Dolls11Gloom$53.33Video Games12Build Your Own Keyboard$77.95Models and HobbyThe Order Details tableDetail IDOrder IDProduct NameProduct IDUnit PriceQuantityDiscount11Build Your Own Keyboard%21Bureaucrat non-action figure2$78.8847.5%32Exercise for Computer Geeks! The DVD!7$14.8864%42The Magical Computer Chip4$32.658052Computer Geeks and Mythical Creatures6$78.504063Access! The Game!5$22.95515%74Programmer Action Figure1$12.9526%84Ultimate Flying Pizza8$36.7584%95External 5.25-inch Floppy Diskette Drive (1/4 Scale)9$65.00410%106Relational Database Diagram3$22.50126.5%117Gloom%127Relational Database Diagram3$22.5049%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.On the Create tab, in the Other group, click Query Design.In the Show Table dialog box, select the tables that you want to work with, click Add, and then click Close after you have finished adding the tables.-or-Double-click the tables that you want to use, and then click Close. Each table appears 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.Double-click the table fields that you want to use in your query.As a rule, you add only the group field and the value field to the query. However, you can use a calculation instead of a value field — the next steps explain how to do so.Add the Category field from the Products table to the design grid.Create 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)Make sure 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.On the Design tab, in the Show/Hide group, click Totals.The Total row appears in the design grid, and in that row, Group By appears in the first and second columns.In the second column, change the value in the Total row to Sum. The Sum function adds the individual sales figures.Click Runto run the query and display the results in Datasheet view.Keep 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.
![Sum Of Products Sum Of Products](/uploads/1/2/5/3/125393672/494054882.jpg)
The steps in this section explain how to use three types of criteria:.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.Criteria that hide certain totals after calculating them. For example, you can display only the totals greater than $150,000.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.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.In the Criteria row of the Category ID column, type =Dolls Or Sports or Art and Framing.Click Runto run the query and display the results in Datasheet view.Switch back to Design view and, in the Criteria row of the Total Sales Value column, type 100.Run the query to see the results, and then switch back to Design view.Now add the criteria to exclude individual sales transactions that are less than $100. To do this, you need to add another column.
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 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.Sum data across multiple groups by using a crosstab queryA crosstab query is a special type of query that displays its results in a grid similar to an Excel worksheet. Crosstab queries summarize your values and then group them by two sets of facts — one set down the side (a set of row headers), and the other across the top (a set of column headers).