Which aggregate function will find the customer with the largest amount paid?
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. Show
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:
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 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 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 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
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
Create database tables from the worksheets
Rename the primary key fieldsNote: 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.
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 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. Create a basic select query
Add a Total row
Hide a Total row
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 queryA grand total is the sum of all the values in a column. You can calculate several types of grand totals, including:
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
Calculate a grand total that excludes some records
Top of Page 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 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
Top of Page 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). This figure illustrates part of the result set for sample crosstab query:
As you proceed, remember that a crosstab query does not always populate all the fields in the result set because the tables that you use in the query do not always contain values for every possible data point. When you create a crosstab query, you typically include data from more than one table, and you always include 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 explain how to create a crosstab query that groups total sales by city. The query uses two expressions to return a formatted date and a sales total. Create a crosstab query
Top of Page Aggregate function referenceThis table lists and describes the aggregate functions that Access provides in the Total row and in queries. Remember that Access provides more 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 use the larger set of aggregate functions that SQL Server provides. For more information about that set of functions, see Microsoft SQL Server Books Online.
Top of Page What aggregate function is used to return the number of records in a group?Using the COUNT function, we can query the database to return the total number of rows in a table. Aggregate functions are used only after the SELECT clause.
Which of the following aggregate functions can be reasonably applied to the wildcard (*) character rather than the name of particular column?The COUNT function can take the wildcard character (*) as an argument.
What is the data type he should use with the Social Security number field and why?What data type should Joe use for the Social Security Number field and why? Joe should use the Short Text data type. Even though the field will contain numbers, there will not be any calculations or arithmetic associated with them. The caption for the Start Date field should be Started when the query results display.
What is a collection of related tables?A database is a collection of organized tables. Each table is a collection of related records. Each record gives information about one item and is made up of many fields. Each field is one specific piece of information.
|