Adding a Calculated Field to a Query

Database fields generally display the data that is entered into them. However, a calculated field will automatically figure its value based on values in other fields. Queries often include calculated fields that display values based on other values returned by the query.

To enter formulas in calculated fields:

1. Open the query in Design view.
2. Enter a new calculated field. Begin by typing the name of the field, followed by a colon (e.g., Total:).
3. Now enter the expression to be calculated. When referring to a field name, enclose the name in brackets. You can use standard mathematical operator symbols like * (multiplication), / (division), + (addition), - (subtraction), and ^ (exponentiation).
4. Run the query to see the results of the calculated field.
5. You can also use operators to manipulate text fields.

When using a calculated field to combine text fields, you usually want to hide the text fields that are being combined and show only the new calculated field.

For queries that reference more than one table, you should preface the field name with the table name, with an exclamation point in between. For example, to create a new field called Total that equals the value of the Hours field from the TimeSheet table times 40, you would use the following:

          Total:[TimeSheet]![Hours]*40

Notice that both the table name and the field name are enclosed in brackets.

You can also use operators to manipulate text fields. For example, you can use a calculated field to display a new field that combines the values of the FirstName and LastName fields. To do this, use the ampersand (&) symbol to concatenate, or join, the fields. The expression might look like this:

    FullName: [FirstName] & " " & [LastName]

Using the " " space between field names will insert a space between the values of FirstName and LastName.