Part III: More-Advanced Access Techniques
638
l (^) Number of records: Finding a specific number of records returned by a query.
l Total: Calculate summary and other statistics on fields selected by the query.
l (^) Crosstab: Summarize data in an easy-to-read, row-and-column format.
On the CD-ROM
This chapter uses the database named Chapter18.accdb on the CD-ROM accompanying this book. If you
have not already copied it onto your machine from the CD, you should do so now.
Using Calculated Fields
Queries are not limited to displaying fields from tables. Your queries can also incorporate calcu-
lated fields that display the result of mathematical or other operations on the content of a field (or
fields). A calculated field can be created in many different ways, including the following:
l (^) Concatenating two text type fields using the ampersand (&).
l Performing a mathematical calculation on two numeric fields.
l (^) Creating a new field based on data returned by an Access function.
In the next example, you create a calculated field, DiscountPrice, from the Price and
DiscountPercent fields in tblSalesLineItems:
- Create a new query containing tblSalesLineItems.
- Add InvoiceNumber, Description, Price, and DiscountPercent to the QBE grid.
- Move to an empty Field cell of the QBE pane.
- Press Shift+F2 to open the Zoom box (or right-click and select Zoom).
- Enter the following into the Zoom box (be sure to include the colon after DiscountPrice):
DiscountPrice: tblSalesLineItems.Price -
tblSalesLineItems.Price * tblSalesLineItems.DiscountPercentThe Zoom box should look like Figure 18.1.
FIGURE 18.1
Creating a simple calculated field