Part III: More-Advanced Access Techniques
656
Expression
You use an expression to tell Access to derive a calculated field by using one or more aggregate
functions in the Field cell of the QBE pane. For example, you may want to create a query that lists
each customer and how much money the customer saved, based on the individual’s discount rate.
This query requires creating a calculated field that uses a sum aggregate against the Price field in
tblSalesLineItems, which is then multiplied by the DiscountPercent field in tbl-
SalesLineItems.Cross-Reference
We discuss this type of calculation in detail in the section titled “Creating expressions for totals,” later in this
chapter.
Using a Where clause
The Where options tells Access that you want to specify a filter against other fields in the query.
The filtering operation is performed before the aggregate functions are executed. For example, you
may want to create a query that counts all products that cost more than $50. In this case, you
would apply a Where clause to the RetailPrice field, and the query would then count only
those products where the RetailPrice exceeds $50. This type of operation is also discussed in
detail in the “Specifying criteria for a total query” section, later in this chapter.One thing to notice is that it is possible to specify criteria for a field that is also set as a Group By
field. Later in this chapter, in the “Specifying criteria for a Group By field” section, you’ll see exam-
ples where a single field is used both for grouping and for filtering the query results.Aggregate functions
You can choose from a total of nine aggregate functions: Sum, Avg, Min, Max, Count, StDev, Var,
First, and Last. Each option performs an operation on the grouped data in the query (Table 18.4
describes how to use each option) and supplies the new data to a cell in the results. Aggregate
options are what database designers think of when they hear the words total query.For example, you may want to determine the maximum (Max), minimum (Min), and average
(Avg) price of each type of product in tblProducts. There can be only one maximum value for
all products. Several products may have the same maximum value, but only one price is the larg-
est. You use aggregate queries to answer important business questions about the application’s data.You can also use Max, Min, or Avg to return a single aggregate value based on all the records in a
table, without grouping the records.Some of the aggregate options can be performed only against certain types of fields. For example,
you cannot perform a Sum option against Text type data, nor can you use a Max option against an
OLE object.Table 18.4 lists each aggregate function, what it does, and which field types you can use with the
option.