Show All

Create a calculated field in a query (MDB)

Note  The information in this topic applies only to a Microsoft Access database (.mdb).

You can create a new field that displays the results of a calculation you define with an expression or that manipulates field values.

1. Open the query in Design view.

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, 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 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, change Group By to Expression.

   

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

   How?

   In the query design 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 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.