Microsoft Office Tutorials and References
In Depth Information
When you run the query, you will get output for nonsalaried employees only, as shown in Figure B-30.
Query output for nonsalaried workers only
Thus, it is possible to use Criteria in a Totals query, just as you would with a
query. You do not
need to print or save the query output, so return to Design view and close the query.
AT THE KEYBOARD
Assume that you want to see two pieces of information for hourly workers: (1) the average wage rate, which
you will call Average Rate in the output; and (2) 110 percent of the average rate, which you will call the
Increased Rate. To get this information, you can make a calculated field in a new query from a Totals query.
In other words, you use one query as a basis for another query.
Create the first query; you already know how to perform certain tasks for this query. The revised heading
for the average rate will be Average Rate, so type Average Rate: Wage Rate in the Field cell. Note that you
want the average of this field. Also, the grouping will be by the Salaried field. (To get hourly workers only,
enter Criteria: No.) Confirm that your query resembles that in Figure B-31, then save the query and close it.
A totals query with average
Now begin a new query. However, instead of bringing in a table to the query design, select a query. To
start a new query, click the Create tab and then click the Query Design button in the Queries group. The
Show Table dialog box appears. Click the Queries tab instead of using the default Tables tab, and select the
query you just saved as a basis for the new query. The most difficult part of this query is to construct the
expression for the calculated field. Conceptually, it is as follows:
Increased Rate: 1.1 * [The current average]