Access 2010

Page 1

How to create calculated fields and totals rows in tables

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 this 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 other, or to subtract a constant value from a field

In our example, we'll use a table containing a month's worth of orders. 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 seven dozen fudge brownies have been sold, and that a dozen equals 12 brownies.

To find the total number of brownies that have been sold, we'd have to multiply the number of units sold by the numerical value of that unit—here, 7*12, which equals 84. That was a simple problem, but making that 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 each row.

To create a calculated field:

1. Select the Fields tab, locate the Add & Delete group, and click the More Fields drop-down command.
   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 name in the Expression Categories box. Remember to include mathematical operators like + or -. Because we want to multiply our two field, we put the multiplication symbol (*) between the two field names, which we selected from the Expression Categories box.
   Building the expression for a calculated field
4. Click OK. The calculated field will be added to your table. If desired, you can now sort or filter it like any other field.
   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.

 

Totals rows

The totals row lets you add 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 let us know the total number of items sold.

To create a totals row:

1. Select the Home tab, then locate the Data group.
2. Click the Totals command.
   The Totals command
3. Scroll down to the last row of your table.
4. Locate the field you want to create a totals row for, then select the second empty cell below it. When a drop-down arrow appears, click it.
   The totals row drop-down arrow
5. Select the function you would like to be performed on the field data.
   Selecting the function to be performed on the field
6. Your field total will appear.
   The totals row, showing us the total number of products sold