Show All

Hide All

You can create a new field that displays the results of a calculation you define with an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) or that manipulates field values.

1. Open the query in Design view (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.).

2. Type an expression in an empty cell in the Field row. If the expression includes a field name, you must place brackets around the name.

   

   After you press ENTER or move to another cell, Microsoft Access enters the default field name ExprN, where N is an integer incremented for each new expression field in the query. The name appears before the expression and is followed by a colon. In a datasheet (datasheet: Data from a table, form, query, view, or stored procedure that is displayed in a row-and-column format.), this name is the column heading.

   

   You can select ExprN and type a more descriptive name, such as NewPrice.

3. If the expression includes one or more aggregate functions (Sum, Avg, Count, Min, Max, StDev, or Var), and if 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.) includes another field or fields that you want to use for grouping, click Totals on the toolbar (unless the Total row is already displayed). Leave Group By in the Total cell for the grouping field, and in the calculated field (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.), change Group By to Expression.

   

4. If you want, enter criteria to affect results of the calculation.

   How?

   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.

   Limit groups before performing calculations on groups of records

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

     

     This query totals extended prices for ...

     ... companies in Canada and the UK only.

   Return selected results after calculations are performed

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

     

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

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

   Limit records before they are grouped and before the calculation is performed

   • Add to the design grid the field whose records you want to limit, and then specify criteria in the field's Criteria cell.

     If you're calculating totals in the same query, set the Total cell for the field containing the criteria to Where. This rule applies whether you're calculating the total on all records or groups of records. (Microsoft Access automatically clears the Show check box.)

     The following example uses the ExtendedPrice field twice, once to limit the records and once to calculate the total. However, you can use a different field to limit records by dragging that field to the design grid and setting its Total cell to Where.

     

     From these records, the query retrieves only those with extended prices greater than $500.00 before it groups or totals ...

     ... and then it sums and shows only those totals for companies in Canada or the UK.

     This total for Seven Seas Imports does not include the order for $210.00.

  Notes  

• The Order Details Extended query in the Northwind sample database has an example of a calculated field called ExtendedPrice. The Order Subtotals query has an example of a calculated field called Subtotal that calculates on groups of records. To view these queries, open the Northwind database in your Office folder's Samples subfolder and then open the Order Details Extended or Order Subtotals query in Design view.

• You might want to set field properties, such as the Format property, since the field doesn't inherit properties from the underlying table.

• Calculation query, how to avoid extreme values?
• How to sort on calculated field?
• How do I get the minimum of 4 different calculations in Access?