Microsoft Access 2010 Bible

Part III: More-Advanced Access Techniques

648

in the query’s results. You may, for instance, want to join the city, state, and zip fields from a table

as a single field named CSZ (City-State-Zip), rather than dealing with three separate fields on forms

and reports. As you have already seen, an alias is useful for calculated fields or calculating totals.

Access automatically assigns default names such as Expr1 or AvgOfWeight, but it’s easy to pro-

vide your own aliases for fields in Access queries.

Note
If you specified a Caption for the field in the table designer, this name is used in the query’s results.

As you saw earlier with query fields created from expressions (such as FullName: FirstName &

“ “ & LastName), field aliases are constructed by providing the field alias, followed by a colon

and the expression specifying the field’s contents. To create the CSZ aliased field, follow these

steps:

1. Create a new query and select tblCustomers, and then add the Company, Phone, and
   Fax fields to the query.


2. Click in an empty column and enter the following expression:

CSZ: [City] & “, “ & [State] & “ “ & [ZipCode]

Figure 18.12 shows the new field and its alias. This query (qryUsingFieldAlias) is
included in the Chapter18.accdb sample database.

FIGURE 18.12

A query field with the city, state, and zip code combined

This same technique is easily applied to individual fields. Although, in most cases, the user never

sees the actual field name, using an alias can make an application much easier to work with, espe-

cially if the field names in the underlying tables are abbreviated or technical in nature.

Tip
When naming a query field, delete any names assigned by Access (to the left of the colon). For example, be
sure to remove the name Expr1 when you provide an alternate name for the calculated field.