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.
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:
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.
For more examples of mathematical expressions that can be used to create calculated fields, review the arithmetic expressions in the Expression Builder dialog box.
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.