Creating Queries  «Prev 

Zoom Box

Some expressions can get long, and the query grid does not give you much room to see them. Fortunately, Access has a nifty feature called a Zoom Box that displays your expression so that you can see it while you work on it. To display your expression in a Zoom Box, place the cursor somewhere in the expression and press Shift+F2. Click OK on the Zoom Box to return to Design view.


Calculated Fields

For many years, we have created calculated columns using an expression in a query. In many cases, calculating the value each time in a query is desired as values in a row may change. In this case, each time you run the query, the expression is reevaluated for all rows. Since the expression is evaluated every time, this may slow down the query depending on the number of rows and the complexity of the calculation.
In Access 2010, a new type of field was added called a calculated field. In a calculated fi eld, the expression for the calculation is defined in the field, and the calculated values are stored in the table. These values are updated only when a field used in the expression is updated, making calculated fields nearly as fast as other fields. At first glance, it appears that storing calculated values would violate database normalization rules. This is true.
However, there are scenarios when data may not change very often where this type of denormalization is valuable. For example, first and last name are often concatenated to form a person's full name.
Names may not change very often, so this example is a good candidate for using the new calculated field type. Also, calculated fields can be indexed if desired, allowing faster retrieval and sorting of records based on the calculated value.