Access total query

Access total query DEFAULT

Access 2013: More Query Design Options

Lesson 12: More Query Design Options

/en/access2013/designing-a-multitable-query/content/

Introduction

Access offers several options that let you design and run queries that return exactly the information you're looking for. For instance, what if you need to find out how many of something exists within your database? Or what if you would like your query results to automatically be sorted a certain way? If you know how to use Access's query options, you can design almost any query you want.

In this lesson, you'll learn how to modify and sort your queries within Query Design view, as well as how to use the Totals function to create a query that can perform calculations with your data. You'll also learn about additional query-building options offered in Access.

Throughout this tutorial, we will be using a sample database. If you would like to follow along, you'll need to download our Access 2013 sample database. You will need to have Access 2013 installed on your computer in order to open the example.

Modifying queries

Access offers several options for making your queries work better for you. In addition to modifying your query criteria and joins after you build your queries, you can choose to sort and hide fields in your query results.

To modify your query:

When you open an existing query in Access, it is displayed in Datasheet view, meaning you will see your query results in a table. To modify your query, you must enter Design view, the view you used when creating it. There are two ways to switch to Design view:

  • On the Home tab of the Ribbon, click the View command. Select Design View from the drop-down menu that appears.
    Screenshot of Access 2013Switching to Design View with the View command on the Ribbon
  • In the bottom-right corner of your Access window, locate the small view icons. Click the Design View icon, which is the icon farthest to the right.
    Screenshot of Access 2013Switching to Design View using the View Icon

Once in Design view, make the desired changes, then select the Run command to view your updated results.

You may notice that Access also offers SQL view. You can ignore this. SQL view allows you to create advanced functions that you will not need to use for this tutorial or for most Access functions.

Sorting queries

Access allows you to apply multiple sorts at once while you're designing your query. This allows you to view your data exactly the way you want.

A sort that includes more than one sorted field is called a multilevel sort. A multilevel sort allows you to apply an initial sort, then further organize data with additional sorts. For instance, if you had a table full of customers and their addresses, you might choose to first sort the records by city, then alphabetically by last name.

When more than one sort is included in a query, Access reads the sorts from left to right. This means the leftmost sort will be applied first. In the example below, customers will be sorted first by the City where they live and then by the Zip Code within that city.

Screenshot of Access 2013A multi-level sort. The records will be sorted by city first.

To apply a multilevel sort:

  1. Open the query and switch to Design view.
  2. Locate the field you want to sort first. In the Sort: row, click the drop-down arrow to select either an Ascending or Descending sort.
    Screenshot of Access 2013Applying an Ascending sort to a field
  3. Repeat the process in the other fields to add additional sorts. Remember, the sorts are applied from left to right, so any additional sorts must be applied to fields located to the right of your primary sort. If necessary, you can rearrange the fields by clicking the top of a field and dragging it to a new location.
    Screenshot of Access 2013Creating a multi-level sort
  4. To apply the sort, click the Run command.
    Screenshot of Access 2013Clicking Run command
  5. Your query results will appear with the desired sort.
    Screenshot of Access 2013The sorted query results

You can also apply multilevel sorts to tables that don't have queries applied to them. On the Home tab on the Ribbon, select the Advanced drop-down command in the Sort & Filter group. From the menu that appears, select Advanced Filter/Sort and create the multilevel sort as you normally would. When you're finished, click the Toggle Filter command to apply your sort.

Screenshot of Access 2013Clicking the Advanced Filter/Sort command

Hiding fields within queries

Sometimes you might have fields that contain important criteria, but you might not need to actually see the information from that field in the final results. For example, take one of the queries we built in our last lesson: a query to find the names and contact information of customers who had placed orders. We included Order ID numbers in our query because we wanted to make sure we only pulled customers who had placed orders.

However, we really didn't need to see this information in our final query results. In fact, if we were just looking for customer names and addresses, seeing the order number mixed in there might have been distracting. Fortunately, Access makes it easy to hide fields while still including any criteria they contain.

To hide a field within a query:

  1. Open the query and switch to Design view.
  2. Locate the field you want to hide.
  3. Click the checkbox in the Show: row to uncheck it.
    Screenshot of Access 2013Unchecking a field to hide it
  4. To see the updated query, select the Run command. The field will be hidden.

To unhide a hidden field, simply return to Design view and click the checkbox in the field's Show: row again.

More types of queries

By this point, you should understand how to create a simple one-table or multi-table query using multiple criteria. Additional queries offer you the ability to perform even more complex actions with your database. One of these is the totals query, which lets you perform calculations with your data.



Totals queries

Sometimes setting simple criteria won't give you the results you need, especially when you're working with numerical values. You may want to see your query results grouped or counted in some way. For example, let's say we want to find out how many of each menu item at our bakery has been ordered—how many Almond Croissants, Apple Pies, and so on. To do this, we could create a totals query to find the sum of the quantities for each item.

First, the totals query will group all similar menu items from separate orders (for example, Almond Croissants). Then, the Sum function will add the values in the Quantity field to calculate the total number sold for that item.

An illustration of a totals query: The ungrouped data shows every occurance of each item. Our totals query groups all like valies together. The Sum function shows us how many of each item has been ordered.

The Sum function helped us find the desired information in this example, but in other situations you may need to use a different function to find the answer you need. There are several functions you can choose from:

  • Count: Counts the total number of each item
  • Sum: Adds the values together
  • Average: Finds the average of the values
  • Maximum: Returns the highest value
  • Minimum: Returns the lowest value
  • First: Returns the first—or earliest—value
  • Last: Returns the last—or most recent—value

In our example above, we created a subtotal for each menu item in our query. If you wanted to create a grand total for all of the items, you would need to add a totals row. Review our lesson on Modifying Tables to learn how.

To create a totals query:

For our example, we want to find the total number we've sold of each of our menu items, so we'll use a query showing us all of the menu items we've sold. If you want to follow along in our database, open the Menu Items Ordered query.

  1. Create or open a query you want to use as a totals query.
  2. From the Design tab, locate the Show/Hide group, then select the Totals command.
    Screenshot of Access 2013Clicking the Totals command
  3. A row will be added to the table in the design grid, with all values in that row set to Group By. Select the cell in the Total: row of the field you want to perform a calculation on, then click the drop-down arrow that appears.
    Screenshot of Access 2013Selecting the totals row of the field we want to perform a calculation on
  4. Select the calculation you want to be performed in that field. In our example, we want to add the quantities of products we've sold, so we'll select the Sum option.
    Screenshot of Access 2013Setting the Totals calculation to Sum
  5. When you are satisfied with your query design, select the Run command on the Query Tools Design tab to run the query.
    Screenshot of Access 2013Clicking the Run command
  6. The query results will be displayed in the query's Datasheet view, which looks like a table. If you want, save your query by clicking the Save command on the Quick Access toolbar.
    Screenshot of Access 2013The totals query. Note the sums in the far-right field.

More query options

We offer mini-lessons on creating additional types of queries in the last lesson in this tutorial. Below is a list of the queries we currently cover.

  • Parameter query
    A parameter query allows you to create a query that can be updated easily to reflect a new criterion, or search term. When you open a parameter query, Access will prompt you for a search term and then show you query results that reflect that search.
  • Find duplicates query
    A find duplicates query lets you find all duplicate records in your database so you can delete them. Duplicate records can negatively affect the integrity of your database.

Other query-building resources

Challenge!

  1. Open an existing Access database. If you want, you can use our Access 2013 sample database.
  2. Open the Customers Who've Ordered from Nearby Towns query, and switch to Design view.
  3. Add a Totals row to the query.
  4. Set the Totals row in the Orders Table ID field to Count. This will let us count how many orders each customer has placed.
  5. In the Customers table in the Object Relationship pane, double-click the word City to add another City field to the design grid below.
  6. Click and drag the City field you just added so it is to the left of the First Name field. It should now be the leftmost field in the design grid.
  7. Apply the following multilevel sort:
    • In the leftmost City field, apply an ascending sort.
    • In the Last Name field, apply an ascending sort.
  8. Hide the leftmost City field.
  9. Run the query. If you did it correctly, there should be 14 records in the query results. The first record should look like this.

/en/access2013/creating-reports/content/

Sours: https://edu.gcfglobal.org/en/access2013/more-query-design-options/1/

Use Access' Where clause to calculate query totals for specific conditions

In addition to letting you total, average, count, and perform other calculations on entire columns of data, the Total cell in Access' Query Design window allows you to perform those same calculations on only those records where the data meet certain conditions. For example, suppose you would like to know how many Microsoft-certified employees were hired from January 1, 2001 to the present. To create a parameter query that can answer this question, follow these steps:

  1. Open the Employees database.
  2. Click Queries under Objects in the Database window.
  3. Click Create Query in Design View.
  4. Select the Employees Records table, then click Add, and then click Close.
  5. Double-click EmployeeID in the Field list.
  6. Right-click the Sort cell under the EmployeeID Field cell and select Totals.
  7. Click the drop-down arrow of the Total cell and select Count.
  8. Double-click Certified in the field list.
  9. Clear the check mark from the Show cell.
  10. Enter Microsoft in the Criteria cell for the Certified field.
  11. Double-click HireDate in the field list.
  12. Click the drop-down arrow of the Total cell for HireDate and scroll to and select Where.
  13. Clear the check mark from the Show cell.
  14. In the Criteria cell for the Hire Date field, enter: >[Enter Hire Date].
  15. Close and save the query.

When you run the query, you will get a message box that will let you enter the hire date from which you want Access to total the number of Microsoft-certified employees hired. For example, if you enter 1/1/2001, Access will count all the records that have dates later than 1/1/2001 in the Hire Date field and return the total amount of records that satisfy that condition in the query results.

Miss a tip?

Check out the Microsoft Access archive, and catch up on our most recent Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Sours: https://www.techrepublic.com/article/use-access-where-clause-to-calculate-query-totals-for-specific-conditions/
  1. Missoula kenworth
  2. Bowie shooting
  3. 4x4 frames amazon
Calculate totals on ALL the records in a query
  1. Create a select query in Design view
.
  • Definitions:
    • Select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.
    • Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.
    • Criteria: Conditions you specify to limit which records are included in the result set of a query or filter.
  • Add the fields to the query on which you want to perform calculations and specify criteria.
    • Open the "Show Table" dialog box, select the data source, then add the fields you are interested in working with into the "Field" row of the query in design view.
  • Click TotalsButton image on the toolbar. (For Access 2007, have query opened in design view, then look under the "Design" tab on the ribbon for the "Totals" button.)

    Microsoft Access displays the Total row in the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.). Note how before selecting the "Totals" button there was no "Total" row in the QBE grid.

  • For each field in the design grid, click its cell in the Total row, and then click one of the following aggregate functions: Sum, Avg, Min, Max, Count, StDev, or Var, or click the other functions, First and Last.
  • If you want, sort the results.
  • Click ViewButton image on the ribbon to view the results.
  • Calculate totals on GROUPS of records in a query

    1. Create a select query in Design view and add the tables whose records you want to use in the calculation.
    2. Add the fields to the "Fields" row of the QBE screen on which you want to perform calculations, define groupings, and specify criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.).
    3. Click TotalsButton image on the toolbar. (For Access 2007, have query opened in design view, then look under the "Design" tab on the ribbon for the "Totals" button.)

      Microsoft Access displays the Total row in the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.).

    4. For the field or fields you want to group on, leave Group By in the Total cell.
      • IMPORTANT: If you choose to group by more than one field, then the grouping hierarchy will start with the fields to the left and allow you to create primary groups, then group within those.
    5. For each field on which you want to perform a calculation, click its cell in the Total row, and then click one of the following aggregate functions: Sum, Avg, Min, Max, Count, StDev, or Var, or click the other functions, First and Last.
    6. If you want, enter criteria to affect the results of the calculation.

      ShowHow?

      In the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.), where you specify criteria determines when the calculation is performed and can produce different query results.

      Note  Although the following examples only show queries that calculate totals, these guidelines apply to calculated fields (calculated field: A field, defined in a query, that displays the result of an expression rather than displaying stored data. The value is recalculated each time a value in the expression changes.) as well.

      ShowLimit groups before performing calculations on groups of records

      • Specify the criteria in the Group By fields as the following example shows.

        Limit groups of records before performing a calculation on them

        Callout 1 This query totals extended prices for ...

        Callout 2 ... companies in Canada and the UK only.

      ShowReturn selected results after calculations are performed

      • Specify criteria in the field that contains the calculation as the following example shows.

        Perform a calculation and then return the selected results

        Callout 1 This query totals extended prices for companies in Canada and the UK ...

        Callout 2 ... but shows only those that are less than $10,000.

    7. If you want, sort the results.
    8. Click ViewButton image on the toolbar to view the results.
    Sours: https://www.webpages.uidaho.edu/cte419/Offline-Modules/M3/aggregate_totals_in_query.htm
    How to Total \u0026 Percentage - Query Design \u0026 Update - MS Access 2007 - 100% Working

    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

    You 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. 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:

    City

    2003

    2004

    2005

    Paris

    254,556

    372,455

    467,892

    Sydney

    478,021

    372,987

    276,399

    Jakarta

    572,997

    684,374

    792,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 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 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:

    Category

    Dolls

    Games and Puzzles

    Art and Framing

    Video Games

    DVDs and Movies

    Models and Hobbies

    Sports

    The Products table:

    Product Name

    Price

    Category

    Programmer action figure

    $12.95

    Dolls

    Fun with C# (A board game for the whole family)

    $15.85

    Games and Puzzles

    Relational Database Diagram

    $22.50

    Art and Framing

    The Magical Computer Chip (500 Pieces)

    $32.65

    Games and Puzzles

    Access! The Game!

    $22.95

    Games and Puzzles

    Computer Geeks and Mythical Creatures

    $78.50

    Video Games

    Exercise for Computer Geeks! The DVD!

    $14.88

    DVDs and Movies

    Ultimate Flying Pizza

    $36.75

    Sports

    External 5.25-inch Floppy Diskette Drive (1/4 Scale)

    $65.00

    Models and Hobbies

    Bureaucrat non-action figure

    $78.88

    Dolls

    Gloom

    $53.33

    Video Games

    Build Your Own Keyboard

    $77.95

    Models and Hobbies

    The Orders table:

    Order Date

    Ship Date

    Ship City

    Shipping Fee

    11/14/2005

    11/15/2005

    Jakarta

    $55.00

    11/14/2005

    11/15/2005

    Sydney

    $76.00

    11/16/2005

    11/17/2005

    Sydney

    $87.00

    11/17/2005

    11/18/2005

    Jakarta

    $43.00

    11/17/2005

    11/18/2005

    Paris

    $105.00

    11/17/2005

    11/18/2005

    Stuttgart

    $112.00

    11/18/2005

    11/19/2005

    Vienna

    $215.00

    11/19/2005

    11/20/2005

    Miami

    $525.00

    11/20/2005

    11/21/2005

    Vienna

    $198.00

    11/20/2005

    11/21/2005

    Paris

    $187.00

    11/21/2005

    11/22/2005

    Sydney

    $81.00

    11/23/2005

    11/24/2005

    Jakarta

    $92.00

    The Order Details table:

    Order ID

    Product Name

    Product ID

    Unit Price

    Quantity

    Discount

    1

    Build Your Own Keyboard

    12

    $77.95

    9

    5%

    1

    Bureaucrat non-action figure

    2

    $78.88

    4

    7.5%

    2

    Exercise for Computer Geeks! The DVD!

    7

    $14.88

    6

    4%

    2

    The Magical Computer Chip

    4

    $32.65

    8

    0

    2

    Computer Geeks and Mythical Creatures

    6

    $78.50

    4

    0

    3

    Access! The Game!

    5

    $22.95

    5

    15%

    4

    Programmer Action Figure

    1

    $12.95

    2

    6%

    4

    Ultimate Flying Pizza

    8

    $36.75

    8

    4%

    5

    External 5.25-inch Floppy Diskette Drive (1/4 Scale)

    9

    $65.00

    4

    10%

    6

    Relational Database Diagram

    3

    $22.50

    12

    6.5%

    7

    Gloom

    11

    $53.33

    6

    8%

    7

    Relational Database Diagram

    3

    $22.50

    4

    9%

    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, click 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.

    2. 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:

      A new field in a datasheet

    3. 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.

    4. 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. For these sample tables, you should let Access infer the data type.

    5. When you finish entering the data, click Save.

      Keyboard shortcut  Press CTRL+S.

      The Save As dialog box appears.

    6. In the Table Name box, enter the name of the sample table, and then click OK.

      You use the name of each sample table because the queries in the how-to sections use those names.

    7. Repeat these steps until you create each of the sample tables listed 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. Start your spreadsheet program and create a new, blank file. If you use Excel, it creates a new, blank workbook by default.

    2. Copy the first sample table provided above and paste it into the first worksheet, starting at the first cell.

    3. Using the technique provided by your spreadsheet program, rename the worksheet. Give the worksheet the same 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.

      Note: You might need to add worksheets to your spreadsheet file. For information on doing that task, see the help for your spreadsheet program.

    5. Save the workbook to a convenient location on your computer or your network, and go to the next set of steps.

    Create database tables from the worksheets

    1. On the External Data tab, in the Import group, click Excel.

      -or-

      Click More, and then select a spreadsheet program from the list.

      The Get External Data - Program Name Spreadsheet dialog box appears.

    2. Click Browse, open the spreadsheet file that you created in the previous steps, and then click OK.

      The Import Spreadsheet Wizard starts.

    3. By default, the wizard selects the first worksheet in the workbook (the Customers worksheet, if you followed the steps in the previous section), and data from the worksheet appears in the lower section of the wizard page. Click Next.

    4. On the next page of the wizard, click First row contains column headings, and then click Next.

    5. Optionally, on the next page, use the text boxes and lists under Field Options to change field names and data types or to omit fields from the import operation. Otherwise, click Next.

    6. Leave the Let Access add primary key option selected, and click Next.

    7. By default, Access applies the name of the worksheet to your new table. Accept the name or enter another name, and then click Finish.

    8. Repeats steps 1 through 7 until you have created 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 click Design View.

    2. For each table, locate 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. 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.

    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 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

    1. On the Create tab, in the Other group, click Query Design.

    2. Double-click the table or tables that you want to use in your query.

      The selected 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 include fields that contain descriptive data, such as names and descriptions, but you must include a field that contains numeric or currency data.

      Each field appears in a cell in the design grid.

    4. Click RunButton imageto run the query.

      The query result set appears 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 click Design View. You can then adjust the query, as needed, by adding or removing table fields. To remove a field, select the column in the design grid and press DELETE.

    6. Save your query.

    Add a Total row

    1. Make sure that your query is open in Datasheet view. To do so, right-click the document tab for the query and click Datasheet View.

      -or-

      In the Navigation Pane, double-click the query. This runs the query and loads the results into a datasheet.

    2. On the Home tab, in the Records group, click Totals.

      A new Total row appears in your datasheet.

    3. In the Total row, click the cell in the field that you want to sum, and then select Sum from the list.

    Hide a Total row

    • On the Home tab, in the Records group, click 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

    Order ID

    Order Date

    Ship Date

    Ship City

    Shipping Fee

    1

    11/14/2005

    11/15/2005

    Jakarta

    $55.00

    2

    11/14/2005

    11/15/2005

    Sydney

    $76.00

    3

    11/16/2005

    11/17/2005

    Sydney

    $87.00

    4

    11/17/2005

    11/18/2005

    Jakarta

    $43.00

    5

    11/17/2005

    11/18/2005

    Paris

    $105.00

    6

    11/17/2005

    11/18/2005

    Stuttgart

    $112.00

    7

    11/18/2005

    11/19/2005

    Vienna

    $215.00

    8

    11/19/2005

    11/20/2005

    Miami

    $525.00

    9

    11/20/2005

    11/21/2005

    Vienna

    $198.00

    10

    11/20/2005

    11/21/2005

    Paris

    $187.00

    11

    11/21/2005

    11/22/2005

    Sydney

    $81.00

    12

    11/23/2005

    11/24/2005

    Jakarta

    $92.00

    The Order Details table

    Detail ID

    Order ID

    Product Name

    Product ID

    Unit Price

    Quantity

    Discount

    1

    1

    Build Your Own Keyboard

    12

    $77.95

    9

    0.05

    2

    1

    Bureaucrat non-action figure

    2

    $78.88

    4

    0.075

    3

    2

    Exercise for Computer Geeks! The DVD!

    7

    $14.88

    6

    0.04

    4

    2

    The Magical Computer Chip

    4

    $32.65

    8

    0.00

    5

    2

    Computer Geeks and Mythical Creatures

    6

    $78.50

    4

    0.00

    6

    3

    Access! The Game!

    5

    $22.95

    5

    0.15

    7

    4

    Programmer Action Figure

    1

    $12.95

    2

    0.06

    8

    4

    Ultimate Flying Pizza

    8

    $36.75

    8

    0.04

    9

    5

    External 5.25-inch Floppy Diskette Drive (1/4 Scale)

    9

    $65.00

    4

    0.10

    10

    6

    Relational Database Diagram

    3

    $22.50

    12

    0.065

    11

    7

    Gloom

    11

    $53.33

    6

    0.08

    12

    7

    Relational Database Diagram

    3

    $22.50

    4

    0.09

    Calculate a simple grand total

    1. On the Create tab, in the Other group, click Query Design.

    2. Double-click the table that you want to use in your query.

      If you use the sample data, double-click the Orders table.

      The table appears in a window in the upper section of the query designer.

    3. Double-click the field that you want to sum. Make sure that the field is set to either the Number or Currency data type. If you try to sum values in non-numeric fields, such as a Text field, Access displays the Data type mismatch in criteria expression error message when you try to run the query.

      If you use the sample data, double-click the Shipping Fee column.

      You can 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.

    4. On the Design tab, in the Show/Hide group, click Totals. Button image

      The Total row appears in the design grid and Group By appears in the cell in the Shipping Fee column.

    5. Change the value in the cell in the Total row to Sum.

    6. Click RunButton image to run the query and display 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.

    7. Optionally, save the query and close it.

    Calculate a grand total that excludes some records

    1. On the Create tab, in the Other group, click Query Design.

    2. Double-click the Order table and Order Details table.

    3. Add 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, type Date() -1. That expression excludes the current day's records from the calculated total.

    5. 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.

    6. 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.

    7. In the second column, change the value in the cell of the Total row to Sum. The Sum function adds the individual sales figures.

    8. Click RunButton image to run the query and display 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.

      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

    Product ID

    Product Name

    Price

    Category

    1

    Programmer action figure

    $12.95

    Dolls

    2

    Fun with C# (A board game for the whole family)

    $15.85

    Games and Puzzles

    3

    Relational Database Diagram

    $22.50

    Art and Framing

    4

    The Magical Computer Chip (500 Pieces)

    $32.65

    Art and Framing

    5

    Access! The Game!

    $22.95

    Games and Puzzles

    6

    Computer Geeks and Mythical Creatures

    $78.50

    Video Games

    7

    Exercise for Computer Geeks! The DVD!

    $14.88

    DVDs and Movies

    8

    Ultimate Flying Pizza

    $36.75

    Sports

    9

    External 5.25-inch Floppy Diskette Drive (1/4 Scale)

    $65.00

    Models and Hobby

    10

    Bureaucrat non-action figure

    $78.88

    Dolls

    11

    Gloom

    $53.33

    Video Games

    12

    Build Your Own Keyboard

    $77.95

    Models and Hobby

    The Order Details table

    Detail ID

    Order ID

    Product Name

    Product ID

    Unit Price

    Quantity

    Discount

    1

    1

    Build Your Own Keyboard

    12

    $77.95

    9

    5%

    2

    1

    Bureaucrat non-action figure

    2

    $78.88

    4

    7.5%

    3

    2

    Exercise for Computer Geeks! The DVD!

    7

    $14.88

    6

    4%

    4

    2

    The Magical Computer Chip

    4

    $32.65

    8

    0

    5

    2

    Computer Geeks and Mythical Creatures

    6

    $78.50

    4

    0

    6

    3

    Access! The Game!

    5

    $22.95

    5

    15%

    7

    4

    Programmer Action Figure

    1

    $12.95

    2

    6%

    8

    4

    Ultimate Flying Pizza

    8

    $36.75

    8

    4%

    9

    5

    External 5.25-inch Floppy Diskette Drive (1/4 Scale)

    9

    $65.00

    4

    10%

    10

    6

    Relational Database Diagram

    3

    $22.50

    12

    6.5%

    11

    7

    Gloom

    11

    $53.33

    6

    8%

    12

    7

    Relational Database Diagram

    3

    $22.50

    4

    9%

    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, click Query Design.

    2. Select the tables that you want to work with, and then click Add.

      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.

    3. 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.

      1. Add the Category field from the Products table to the design grid.

      2. 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.

      3. 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.

      4. In the second column, change the value in the Total row to Sum. The Sum function adds the individual sales figures.

      5. Click RunButton image to run the query and display the results in Datasheet view.

      6. Keep the query open for use in the next section.

        Use criteria with a totals query

        The 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:

      7. 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.

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

      9. 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

      10. 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.

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

      12. Click RunButton image to run the query and display the results in Datasheet view.

      13. Switch back to Design view and, in the Criteria row of the Total Sales Value column, type >100.

      14. Run the query to see the results, and then switch back to Design view.

      15. 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.

      16. Copy the expression from the second column to the third column.

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

      18. Run the query to see 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*[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

    Sum data across multiple groups by using a crosstab query

    A 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:

    A 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

    Order Date

    Ship Date

    Ship City

    Shipping Fee

    11/14/2005

    11/15/2005

    Jakarta

    $55.00

    11/14/2005

    11/15/2005

    Sydney

    $76.00

    11/16/2005

    11/17/2005

    Sydney

    $87.00

    11/17/2005

    11/18/2005

    Jakarta

    $43.00

    11/17/2005

    11/18/2005

    Paris

    $105.00

    11/17/2005

    11/18/2005

    Stuttgart

    $112.00

    11/18/2005

    11/19/2005

    Vienna

    $215.00

    11/19/2005

    11/20/2005

    Miami

    $525.00

    11/20/2005

    11/21/2005

    Vienna

    $198.00

    11/20/2005

    11/21/2005

    Paris

    $187.00

    11/21/2005

    11/22/2005

    Sydney

    $81.00

    11/23/2005

    11/24/2005

    Jakarta

    $92.00

    The Order Details table

    Order ID

    Product Name

    Product ID

    Unit Price

    Quantity

    Discount

    1

    Build Your Own Keyboard

    12

    $77.95

    9

    5%

    1

    Bureaucrat non-action figure

    2

    $78.88

    4

    7.5%

    2

    Exercise for Computer Geeks! The DVD!

    7

    $14.88

    6

    4%

    2

    The Magical Computer Chip

    4

    $32.65

    8

    0

    2

    Computer Geeks and Mythical Creatures

    6

    $78.50

    4

    0

    3

    Access! The Game!

    5

    $22.95

    5

    15%

    4

    Programmer Action Figure

    1

    $12.95

    2

    6%

    4

    Ultimate Flying Pizza

    8

    $36.75

    8

    4%

    5

    External 5.25-inch Floppy Diskette Drive (1/4 Scale)

    9

    $65.00

    4

    10%

    6

    Relational Database Diagram

    3

    $22.50

    12

    6.5%

    7

    Gloom

    11

    $53.33

    6

    8%

    7

    Relational Database Diagram

    3

    $22.50

    4

    9%

    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

    1. On the Create tab, in the Other group, click Query Design.

    2. Double-click the tables that you want to use in your query.

      Each table appears as a window in the upper section of the query designer.

      If you use the sample tables, double-click the Orders table and the Order Details table.

    3. Double-click the fields that you want to use in your query.

      Each field name appears in the a blank cell in the Field row of the design grid.

      If you use the sample tables, add 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 type 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, click Crosstab.

      The Total row and the Crosstab row appear in the design grid.

    6. Click the cell in the Total row in the City field and select Group By. Do the same for the Ship Date field. Change the value in the Total cell of the Total Sales field to Expression.

    7. In the Crosstab row, set the cell in the City field to 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, click Run.

      The query results appear in Datasheet view.

    Top of Page

    Aggregate function reference

    This 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.

    Function

    Description

    Use with the data type(s)

    Average

    Calculates the average value for a column. The column must contain numeric, currency, or date/time data. The function ignores null values.

    Number, Currency, Date/Time

    Count

    Counts the number of items in a column.

    All data types except complex repeating scalar data, such as a column of multivalued lists.

    For more information about multivalued lists, see the articles Guide to multivalued fields and Create or delete a multivalued field.

    Maximum

    Returns the item with the highest value. For text data, the highest value is the last alphabetic value — Access ignores case. The function ignores null values.

    Number, Currency, Date/Time

    Minimum

    Returns the item with the lowest value. For text data, the lowest value is the first alphabetic value — Access ignores case. The function ignores null values.

    Number, Currency, Date/Time

    Standard Deviation

    Measures how widely values are dispersed from an average value (a mean).

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

    Number, Currency

    Sum

    Adds the items in a column. Works only on numeric and currency data.

    Number, Currency

    Variance

    Measures the statistical variance of all values in the column. You can use this function only on numeric and currency data. If the table contains less than two rows, Access returns a null value.

    For more information about variance functions, see the article Display column totals in a datasheet.

    Number, Currency

    Top of Page

    Sours: https://support.microsoft.com/en-us/office/sum-data-by-using-a-query-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a

    Query access total

    This article explains how to count the data returned by a query. For example, on a form or report, you can count the number of items in one or more table fields or controls. You can also calculate average values, and find the smallest, largest, earliest, and latest values. In addition, Access provides a feature called the Total Row that you can use to count data in a datasheet without having to alter the design of your query.

    What do you want to do?

    Understand ways to count data

    You can count the number of items in a field (a column of values) by using the Count function. The Count function belongs to a set of functions called aggregate functions. You use aggregate functions to perform a calculation on a column of data and return a single value. Access provides a number of aggregate functions in addition to Count, such as:

    • Sum, for summing a column of numbers.

    • Average, for averaging a column of numbers.

    • Maximum, for finding the highest value in a field.

    • Minimum, for finding the lowest value in a field.

    • Standard Deviation , for measuring how widely values are dispersed from an average value (a mean).

    • Variance, for measuring the statistical variance of all values in the column.

    Access provides two ways to add Count and other aggregate functions to a query. You can:

    • Open your query in Datasheet view and add a Total row. The Total Row 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. 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 category you want and then sum the sales figures. In contrast, a Total row calculates grand totals for one or more columns (fields) of data.

    Note: The how-to sections in this document emphasize using the Count 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 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 and how to use a totals query to count data. As you proceed, remember that the Count function works on a larger number of data types than do the other aggregate functions. For example, you can run a Count function against any type of field except one containing complex, repeating scalar data, such as a field of multivalued lists.

    In contrast, 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 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

    Count data by using a Total row

    You add a Total row to a query by opening your query in Datasheet view, adding the row, and then selecting the Count function or another aggregate function, such as Sum, Minimum, Maximum, or Average. The steps in this section explain how to create a basic select query and add a Total row.

    Create a basic select query

    1. On the Create tab, in the Other group, click Query Design.

      Access Ribbon Image

    2. Double-click the table or tables that you want to use in your query, and then click Close.

      The selected table or tables appear as windows in the upper section of the query designer. This figure shows a typical table in the query designer:

      A query with three fields in the design grid

    3. Double-click the table fields that you want to use in your query.

      You can include fields that contain descriptive data, such as names and descriptions, but you must include the field that contains the values that you want to count.

      Each field appears in a column in the query design grid.

    4. On the Design tab, in the Results group, click Run.

      The results of the query are displayed in Datasheet view.

    5. Optionally, go back to Design view and adjust your query. To do so, right-click the document tab for the query and click Design View. You can then adjust the query as needed by adding or removing table fields. To remove a field, select the column in the design grid and press DELETE.

    6. Optionally, save your query.

    Add a Total row

    1. Open your query in Datasheet view. To do so for a database in the .accdb file format, right-click the document tab for the query and click Datasheet View.

      -or-

      For a .mdb file format database created with an older version of Access, on the Home tab, in the Views group, click the arrow below View and click Datasheet View.

      -or-

      In the Navigation Pane, double-click the query. Doing this runs the query and loads the results into a datasheet.

    2. On the Home tab, in the Records group, click Totals.

      Access Ribbon Image

      A new Total row appears below the last row of data in your datasheet.

    3. In the Total row, click the field that you want to sum, and then select Count from the list.

    Hide a Total row

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

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

    Top of Page

    Count data by using a totals query

    You count data by using a totals query instead of a Total row when you need to count some or all of the records returned by a query. For example, you can count the number of sales transactions, or the number of transactions in a single city.

    Typically, you use a totals query instead of a Total row when you need to use the resulting value in another part of your database, such as a report.

    Count all the records in a query

    1. On the Create tab, in the Other group, click Query Design.

      Access Ribbon Image

    2. Double-click the table that you want to use in your query, and then click Close.

      The table appears in a window in the upper section of the query designer.

    3. Double-click the fields that you want to use in the query, and make sure you include the field that you want to count. You can count fields of most data types, the exception being fields that contain complex, repeating scalar data, such as a field of multivalued lists.

    4. 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 row for each field in the query.

    5. In the Total row, click the field that you want to count and select Count from the resulting list.

    6. On the Design tab, in the Results group, click Run.

      The results of the query are displayed in Datasheet view.

    7. Optionally, save the query.

    Count records in a group or category

    1. On the Create tab, in the Other group, click Query Design.

      Access Ribbon Image

    2. Double-click the table or tables that you want to use in your query, and then click Close.

      The table or tables appear in a window in the upper section of the query designer.

    3. Double-click the field that contains your category data, and also the field that contains the values that you want to count. Your query cannot contain other descriptive fields.

    4. 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 row for each field in the query.

    5. In the Total row, click the field that you want to count and select Count from the resulting list.

    6. On the Design tab, in the Results group, click Run.

      The results of the query are displayed in Datasheet view.

    7. Optionally, save the query.

    Top of Page

    Aggregate function reference

    The following table lists and describes the aggregate functions that Access provides for use 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.

    Function

    Description

    Use with the data type(s)

    Sum

    Adds the items in a column. Works only on numeric and currency data.

    Number, Decimal, Currency

    Average

    Calculates the average value for a column. The column must contain numeric, currency, or date/time data. The function ignores null values.

    Number, Decimal, Currency, Date/Time

    Count

    Counts the number of items in a column.

    All data types except those containing complex repeating scalar data, such as a column of multivalued lists.

    For more information about multivalued lists, see the articles Guide to multivalued fields and Create or delete a multivalued field.

    Maximum

    Returns the item with the highest value. For text data, the highest value is the last alphabetic value and Access ignores case. The function ignores null values.

    Number, Decimal, Currency, Date/Time

    Minimum

    Returns the item with the lowest value. For text data, the lowest value is the first alphabetic value and Access ignores case. The function ignores null values.

    Number, Decimal, Currency, Date/Time

    Standard Deviation

    Measures how widely the values are dispersed from an average value (a mean).

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

    Number, Decimal, Currency

    Variance

    Measures the statistical variance of all values in the column. You can use this function only on numeric and currency data. If the table contains less than two rows, Access returns a null value.

    For more information about variance functions, see the article Display column totals in a datasheet.

    Number, Decimal, Currency

    Top of Page

    Sours: https://support.microsoft.com/en-us/office/count-data-by-using-a-query-b84cdfd8-07ba-49a7-b067-e1024ccfcca8
    Access: Creating a Totals Query

    Access 2016: How to Create Calculated Fields and Totals Rows

    Lesson 17: How to Create Calculated Fields and Totals Rows

    /en/access2016/designing-your-own-database/content/

    Introduction

    Calculated fields and totals rows let you perform calculations with the data in your tables. Calculated fields perform calculations using data within one record, while totals rows perform a calculation on an entire field of data.

    Calculated fields

    When you create a calculated field, you are adding a new field in which every row contains a calculation involving other numerical fields in that row. To do this, you must enter a mathematical expression, which is made up of field names in your table and mathematical symbols. You don't need to know too much about math or expression building to create a useful calculated field. In fact, you can write robust expressions using only grade-school math. For instance, you could:

    • Use + to find the sum of the contents of two fields or to add a constant value (such as + 2 or + 5) to a field
    • Use * to multiply the contents of two fields or to multiply fields by a constant value
    • Use - to subtract one field from another or to subtract a constant value from a field

    In our example, we will use a table containing the orders from one month. The table contains items listed by sales unit—single, half-dozen, and dozen. One column lets us know the number sold of each sales unit. Another lets us know the actual numerical value of each of these units. For instance, in the top row you can see that two dozen fudge brownies have been sold and that one dozen equals 12 brownies.

    To find the total number of brownies that have been sold, we'll have to multiply the number of units sold by the numerical value of that unit—here, 2*12, which equals 24. This was a simple problem, but performing this calculation for each row of the table would be tedious and time consuming. Instead, we can create a calculated field that shows the product of these two fields multiplied together on every row.

    To create a calculated field:

    1. Select the Fields tab, locate the Add & Delete group, and click the More Fields drop-down command.
      Clicking the More Fields drop-down command
    2. Hover your mouse over Calculated Field and select the desired data type. We want our calculation to be a number, so we'll select Number.
      Selecting the calculated field type
    3. Build your expression. To select fields to include in your expression, double-click the field in the Expression Categories box. Remember to include mathematical operators like the + or - signs. Because we want to multiply our two fields, we'll put the multiplication symbol (*) between them.
      Building the expression for a calculated field
    4. Click OK. The calculated field will be added to your table. If you want, you can now sort or filter it.

      The calculated field shows the product of the two fields to its left

    For more examples of mathematical expressions that can be used to create calculated fields, review the arithmetic expressions in the Expression Builder dialog box.

    Arithmetic operators in the Expression Builder

    Totals rows

    The totals row adds up an entire column of numbers, just like in a ledger or on a receipt. The resulting sum appears in a special row at the bottom of your table.

    For our example, we'll add a totals row to our calculated field. This will show us the total number of items sold.

    To create a totals row:

    1. From the Home tab, locate the Records group, then click the Totals command.
      Clicking the Totals command
    2. Scroll down to the last row of your table.
    3. Locate the desired field for the totals row, then select the second empty cell below the last record for that field. When a drop-down arrow appears, click it.
      Clicking the totals row drop-down arrow
    4. Select the function you want to perform on the field data. In our example, we'll choose Sum to add all of the values in the calculated field.
      Selecting the function to be performed on the field
    5. The totals row will appear.
      The totals row showing the total number of products sold

    /en/access2016/creating-a-parameter-query/content/

    Sours: https://edu.gcfglobal.org/en/access2016/how-to-create-calculated-fields-and-totals-rows/1/

    Similar news:

    Creating a Totals Query

    A totals query includes a column that performs an aggregate operationsuch as summing or averagingon the values of a particular field. A totals query derives either a single value for the entire dynaset or several values for the records that have been grouped within the dynaset. Table 4.1 outlines the aggregate operations you can use for your totals queries.

    Operation

    Purpose

    Groups the records according to the unique values in the field.

    Sums the values in the field.

    Averages the values in the field.

    Returns the smallest value in the field.

    Returns the largest value in the field.

    Counts the number of values in the field.

    Calculates the standard deviation of the values in the field.

    Calculates the variance of the values in the field.

    Returns the first value in the field.

    Returns the last value in the field.

    Returns a custom total based on an expression in a calculated column.

    Tells Access to use the field's criteria to filter the records before calculating the totals.


    The next few sections show you how to use these operations in your queries.

    More Info

    I adapted some of the material in this chapter from my book Microsoft Access 2003, Forms, Reports, and Queries (Que Publishing, 2004). If you need more detail about queries, forms, or reports, you can check out the book at your local store or online at www.mcfedries.com/AccessForms/.


    Displaying the Total Row in the Design Grid

    Before you can work with the aggregate operations, you need to do one of the following in the query design window:

    • Choose View, Totals.

    • Click the Totals button in the toolbar.

    As shown in Figure 4.1, Access adds the row to the design grid, and each cell contains a list of the aggregate operations.

    Figure 4.1. Choose View, Totals to display the row in the query design grid.


    Downloading This Chapter's Examples

    You'll find the database file that contains this chapter's examples at www.mcfedries.com/OfficeGurus/.


    Setting Up a Totals Query on a Single Field

    In the simplest totals query case, you can apply one of the mathematical aggregate operations to a single field. Access will then display the mathematical result for that field. The following steps are required to create a totals query on a single field:

    1.

    Display the row, if it's not already displayed.

    2.

    In the field's cell, use the drop-down list to click the function you want to use.

    3.

    If you want to restrict the records involved in the aggregate operation, enter the appropriate expression in the field's cell.

    4.

    (Optional) In the dynaset, Access displays in the field header, where is the aggregate operation you chose in step 2 and is the name of the field you're working with. If you'd rather see a more readable name, change the cell to the following, where is the name you want to use:

    5.

    Run the query.

    Figure 4.2 shows the result when the aggregate operation is applied to the UnitsInStock field of Northwind's Products table. As you can see, the datasheet consists of a single cell that shows the result of the aggregate operation.

    Figure 4.2. The datasheet shows only the result of the calculation applied to the single field.


    Setting Up a Totals Query on Multiple Fields

    If you want to see more data in the totals query, you can add more fields:

    • Add other fields and apply any mathematical aggregate operation to each field.

    • Add other copies of the same field and apply different mathematical aggregate operations to each field.

    Note, however, that you can only add fields to which you want to apply an aggregate operation; you can't add nonaggregate fields to the query.

    Figure 4.3 shows a query with five columns. These include a Count of the ProductID field, Sum of the UnitsInStock field, and three operations on the UnitPrice field, , and . Figure 4.4 shows the result.

    Figure 4.3. A totals query showing five aggregate operations on three fields.


    Figure 4.4. The result of the totals query shown in Figure 4.3.


    Filtering the Records Before Calculating Totals

    I mentioned earlier that you can add criteria to any of the aggregate columns, and Access will perform the operation only on the records that match the criteria. What if you want to filter the table based on a field that isn't part of any aggregate operation? You can't include a nonaggregate field in the query results, but it is possible to use a nonaggregate field to filter the records. Here are the steps to follow:

    1.

    Add the nonaggregate field to the query design grid.

    2.

    Clear the field's check box.

    3.

    In the field's cell, drop down the list and click .

    4.

    Add the required expression to the field's cell.

    In this case, Access filters the records based on the criteria and then performs the aggregate operation.

    For example, in the query shown earlier in Figure 4.3, suppose you want to run the aggregate operations on only those products in the Beverages category. To do this, you add the Categories table to the query and then set up the CategoryName field with the operation and the criteria "Beverages," as shown in Figure 4.5. Figure 4.6 shows the results for the filtered records.

    Figure 4.5. A totals query that uses the operation to filter the records before performing the other aggregate operations.


    Figure 4.6. The result of the totals query shown in Figure 4.5.


    Creating a Totals Query for Groups of Records

    In its basic guise, a totals query shows a single total for all the records in a table (or all the records in a subset of the table, depending on whether the query includes criteria). Suppose, however, that you prefer to see that total broken out into subtotals. For example, instead of a simple sum on the UnitsInStock field, how about seeing the sum of the orders grouped by category?

    Grouping your totals requires just two steps:

    1.

    Add the field you want to use for the groupings to the design grid.

    2.

    In the field's cell, drop down the list and click .

    Figure 4.7 shows the query from Figure 4.5 changed so that the CategoryName field is now set up with the operation. Running this query produces the result shown in Figure 4.8. As you can see, Access groups the entries in the Category Name column and displays subtotals for each group.

    Figure 4.7. To group your totals, add the field used for the grouping and click in the cell.


    Figure 4.8. Access groups the records and displays subtotals for each group.


    Grouping on Multiple Fields

    You can extend this technique to derive totals for more specific groups. The general idea is that as you apply the operation to more fields, Access groups the records from left to right.

    For example, suppose you want to see subtotals for each supplier within the categories. You can do this by adding the Suppliers table to the query, adding the SupplierName field to the right of the CategoryName field, and clicking in the cell. Figure 4.9 shows the revised query, and Figure 4.10 shows the result. Access creates the groups from left to right, so the records are first grouped by Category and then by Supplier.

    Figure 4.9. You can refine your groupings by applying the operation to more fields, where the grouping occurs from left to right.


    Figure 4.10. The dynaset produced by the query in Figure 4.9.


    Creating a Totals Query Using a Calculated Field

    So far you've seen aggregate operations applied to regular table fields. However, you can also apply them to calculated fields. For example, you've seen how to use the following expression in the Northwind Order Details table to calculate the extended price of an item given its unit price, quantity, and discount percentage:

    [UnitPrice] * [Quantity] * (1 - [Discount])

    This gives you the total charge per product, but what if you want to know the total charge for the entire invoice? You can calculate this by applying the Sum operation to the calculated field that's based on the previous expression. Also, because Order Details usually includes a number of records for each invoice, you need to group the records by the unique OrderID field to get the per-invoice total. Figure 4.11 shows a query with this setup, and Figure 4.12 shows the results. (For good measure, the query also displays the customer and order date from the joined Orders table and displays the total units in each order.)

    Figure 4.11. To calculate the total charge for each invoice, apply the operation to the calculated Extended Price field and group the Order Details records by OrderID.


    Figure 4.12. The dynaset produced by the query in Figure 4.11.


    Access Converts the Query

    If you apply an aggregate operation to a calculated field in the manner shown in Figure 4.11 and then close and reopen the query, you'll see that Access has changed the calculated field by "moving" the aggregate operation into the field's expression and changing the cell to . Access has converted the totals query so that it uses an aggregate function. See the next section for an explanation of the aggregate functions.


    Creating a Totals Query Using Aggregate Functions

    The collection of Access built-in functions also includes a category called SQL Aggregate that includes all the mathematical aggregate operations. There are nine aggregate functions in all, as shown in Table 4.2.

    Function

    Returns

    )

    The average of the values in

    )

    The sum of the values in

    )

    The smallest value in

    )

    The largest value in

    )

    The number of values in

    )

    The standard deviation of the values in where those values are a sample of a larger population.

    )

    The standard deviation of the values in where those values represent the entire population.

    )

    The variance of the values in where those values are a sample of a larger population.

    )

    The variance of the values in where those values represent the entire population.


    The most straightforward way to use an aggregate function is to apply it to a single field using an expression in the cell instead of entering an operation in the cell. For example, instead of clicking in the cell of the UnitsInStock field, you can use the expression in the cell (see Figure 4.13):

    Sum([UnitsInStock])

    Figure 4.13. This query uses the function to calculate the sum of the UnitsInStock field without using the row.


    The advantage here is that you don't need to display the row, so your query is a little less cluttered. (In fact, you must turn off the totals to use this method; otherwise, Access will convert the function to a aggregate operation.)

    You can also include in the query other calculated fields that use aggregate functions. For example, if you also want to know the maximum unit price, you can create a second calculated field that uses the following aggregate expression:

    Max([UnitPrice])

    Combining Aggregate Functions and Totals

    The problem with using the aggregate functions without the row is that there's no function that's equivalent to the operation, so you can't group the records. If you need to use the operation, or if you want to filter the records before the aggregate calculation by using the operation, you need to use the Totals feature.

    This means you can't apply the aggregate functions on a single field, because Access will just convert the function to an operation in the row. However, it does mean that you're free to create custom totals. These are totals that you create yourself by building expressions that combine one or more aggregate functions with the other query operators and operands. This is a calculated field, so you enter the expression in the cell. Note, too, that you must also choose in the cell. Figure 4.14 shows the query from Figure 4.11 converted to use the following aggregate function expression:

    Sum([UnitPrice] * [Quantity] * (1 - [Discount]))

    Figure 4.14. The Sum of Extended Price calculated field uses the function in the cell and in the cell.


    Calculating Units Left In Stock

    If you manage inventory, you always need to know how many units of each product you have left in stock. You might take a physical inventory once or twice a year, but in between these counts you still need to keep tabs on the stock in case you need to reorder. The easiest way to do that is to take the existing number of units in stock and subtract the day's order quantities. The result is the number of units left in stock. Figure 4.15 shows a query set up to make this calculation using the Northwind sample database.

    Figure 4.15. This query uses a custom total to calculate the number of units left in stock after subtracting a day's orders from the current inventory.


    Following are some features of this query to note:

    • In case a reorder is necessary, the records are grouped first by SupplierID and then by ProductName (both from the Products table).

    • The query also includes the UnitsInStock field and the Sum of the Quantity field from Order Details.

    • The Left In Stock calculated field is set up with the operation in the cell and uses the following expression in the cell to create a custom total:

      [UnitsInStock] - Sum([Quantity])
    • The records are filtered to include only those orders from a specific date, using the operation applied to the nonaggregate OrderDate field.

    Figure 4.16 shows the results.

    Figure 4.16. The dynaset produced by the query in Figure 4.15.


    Sours: https://flylib.com/books/en/2.838.1.30/1/


    1174 1175 1176 1177 1178