Microsoft Access 2010 Bible

Chapter 18: Advanced Access Query Techniques

649

If you rename a field, Access uses only the new name for the field’s heading in the query’s data-

sheet and does the same with the control source in forms and reports using the query. New forms

or reports you create on the basis of the query use the new field name. (Access does not change the

actual field name in the underlying table.)

When working with renamed fields, you can use an expression name (the new name you specified)

in another expression within the same query. For example, many queries have a field called

FullName that uses an expression to combine the first and last names fields. You created this type

of field in an earlier query.

Note
When you work with aliased field names, you cannot have criteria that reference the field alias.

Generally speaking, Access accepts virtually any alias you provide, as long as it conforms to the

naming rules applied to tables, fields, and other database objects. Do be aware, however, that load-

ing up queries with a lot of aliases may cause problems later on. Because a field alias masks the

name of the underlying field, it can be difficult to figure out the source of data on a form or report.

In most cases, unless there is a compelling reason to alias the name of a field, you only complicate

maintenance efforts on any form or report using the query’s data. In some situations, however,

using field aliases is the only way to complete certain tasks. For instance, when you’re upsizing an

Access application to SQL Server, because of the stringent field naming requirements in SQL

Server, you may use an aliasing query to convert the Access field names to names that are accept-

able to SQL Server. Similarly, fields coming back to Access from SQL Server may be aliased to con-

vert the field names to names recognized by the Access application. Rather than updating all the

forms and reports in the Access application, it’s easier to use an aliasing query to apply names rec-

ognized by the Access forms and reports to the SQL Server fields.

Query Design Options

Several specifiable default options are available when working with a query design. These options

can be viewed and set by selecting File ➪ Options and then selecting the Object Designers tab.

Figure 18.13 shows this Options dialog box.

These items are relevant to Access queries:

l Show Table Names

l (^) Output All Fields
l Enable AutoJoin
l (^) Query Design Font
l SQL Server Compatible Syntax (ANSI 92)
These options are detailed in Table 18.1.