Part III: More-Advanced Access Techniques
644
The Count(*) function can also be used to determine the total number of records that match a
query’s criteria. For example, the following example shows how many contacts you have in
tblContacts that live in Connecticut:- Start a new query and select tblContacts.
- Click the first empty Field cell in the QBE pane and enter Count(*) in the cell.
- Double-click State in the table to add it to the query.
- Deselect the Show cell for the State field.
- Type CT in the Criteria cell for State.
- Replace Expr1: in the first column with Customer Count in CT.
Figure 18.8 shows how the query should look. Selecting Datasheet from the View button
displays one cell in the datasheet containing the number of customers in Connecticut.
FIGURE 18.8
A query that shows the number of customers in CTRemember that only the field containing the Count(*) function can be shown in the datasheet.
Access reports an error if you try to display any additional fields.Later in this chapter, you’ll read about using grouping options to Access queries. Grouping enables
you to perform aggregate operations (such as counting records) on groups of records. For example,
grouping enables you to count all the products in each product category, or count the number of
customers in each state.Finding the Top (n) Records in a Query
Access provides the capability of finding the first (n) records (that is, a set number or percentage of
its records) that meet a query’s criteria.