How to create calculated and total rows fields in Access 2016

In MS Access, calculated fields and Totals row allow you to perform calculations from data in your tables. Calculated fields perform calculations using data in a record, while the total rows perform calculations on the entire data field.

Field is calculated

When you create a calculated field, you are adding a new field in which each row contains a calculation involving other number fields in that row. To do this, you must enter a mathematical expression, made up of the field names in your table and mathematical symbols. You don't need to know too much about math or build expressions to create a useful calculated field. In fact, you can write strong expressions, just by using the math knowledge you learned at school. For example, you can:

1. Use the + sign to calculate the sum of two fields or to add a constant value (such as + 2 or + 5 ) to the field.
2. Use * to multiply the values ​​of two fields or to multiply the fields with a constant value.
3. Use - subtract one field from another or subtract a constant value from a field.

In the example below, we will use a table that contains orders in a month. The table contains items listed by sales unit - one, half a dozen and a dozen. A column tells us the number of items sold by each sales unit. Another column tells us the actual numerical value of each of these units. For example, in the top row, you can see that two dozen fudge brownies have been sold and each dozen has 12 brownies.

To find the total number of brownie sold, we will have to multiply the number of units sold with the value of each unit (here 2 * 12 equals 24). This is a simple matter, but doing this calculation for each row in the table would be a tedious and time-consuming task. Instead, you can create a calculated field to know how many of these two products are multiplied on each row.

How to create a calculated field

1. Select the Fields tab , locate the Add & Delete group and click the More Fields drop-down command .

2. Hover over the Calculated Field and select the desired data type. Because the result will be a number, so choose Number.

3. Build your expression: To select the fields to include in your expression, double-click the field in the Expression Categories box . Remember to include operators in math like + or - . Because we want to multiply two fields, we will put the multiplication symbol ( * ) between them.

4. Click OK. Calculated fields will be added to your table. If you want, you can sort or filter it.

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

Total goods

The row totals the entire value in a numeric column, like what you usually do in a ledger or on a receipt. The total result will appear in a special row at the bottom of your table.

For the example in this article, we will add the total row to the calculated field. This will show us the total number of items sold.

How to create a total row

1. From the Home tab , locate the Records group , then click the Totals command .

2. Scroll to the last row of the table.

3. Locate the desired field for the total row, then select the second blank below the last record for that field. When a drop-down arrow appears, click the arrow.

4. Select the function you want to perform on the field data. In the example in this article, we will select Sum to add all the values ​​in the calculated field.

5. The total row will appear.

Good luck!

See more:

1. Design your own database in Access 2016
2. Format forms in Access 2016
3. Create forms (forms) in Access 2016