Database Reference
In-Depth Information
EXAMPLE 7
43
List the number, name, and available credit for all customers.
Available credit is computed by subtracting the balance from the credit limit. Because there is no avail-
able credit field in the Customer table, you must calculate it from the existing Balance and CreditLimit fields.
To include a computed field in a query, you enter a name for the computed field, followed by a colon, and
then followed by an expression in one of the columns in the Field row.
To calculate available credit, you can enter the expression AvailableCredit:CreditLimit-Balance in the
desired Field row in the design grid. When entering an expression in the design grid, the default column size
prevents you from being able to see the complete expression. An alternative method is to right-click the
column in the Field row to display the shortcut menu, and then click Zoom to open the Zoom dialog box.
Then you can type the expression in the Zoom dialog box, as shown in Figure 2-15.
Expression
Computed field
(AvailableCredit)
The AvailableCredit
field will appear here
in the design grid
FIGURE 2-15
Using the Zoom dialog box to add a computed field to a query
NOTE
When a field name contains spaces or SQL reserved words, you must enclose the field name in square brackets ([ ]). For
example, if the field name were Credit Limit instead of CreditLimit, you would enter the expression as
[Credit Limit]-Balance.
You can also enclose a field name that does not contain spaces in square brackets, but you do not need to do so.
