Microsoft Office Tutorials and References
In Depth Information
CREATING QUERIES
NOTE
In the query in Figure B-19, the calculated field column was widened so you could see the whole expression. To widen a
column, click the column boundary line and drag to the right.
Run the query. The output should be similar to that in Figure B-20, if you formatted your calculated field
to Currency.
FIGURE B-20
Query output for wages owed to hourly employees for Week 2
Notice that it was not necessary to pull down the Wage Rate and Hours fields to make the query work.
You do not need to save or print the query output, so return to Design view and close the query.
Summarizing Data from Multiple Records (Totals Queries)
You may want data that summarizes values from a field for several records (or possibly all records) in a table.
For example, you might want to know the average hours that all employees worked in a week or the total
(sum) of all of the hours worked. Furthermore, you might want data grouped or stratified in some way. For
example, you might want to know the average hours worked, grouped by all U.S. citizens versus all non-U.S.
citizens. Access calls such a query a Totals query. These queries include the following operations:
Sum
Thetotalofagivenfield
svalues
Count
A count of the number of instances in a field
that is, the number of records. In the
current example, you would count the number of employee IDs to get the number of
employees.
Average
The average of a given field
svalues
Min
The minimum of a given field
svalues
Var
The variance of a given field
svalues
StDev
The standard deviation of a given field
s values
Where
The field has criteria for the query output
AT THE KEYBOARD
Suppose you want to know how many employees are represented in the example database. First, bring the
Employee table into the QBE screen. Because you will need to count the number of employee IDs, which is a
Totals query operation, you must bring down the Employee ID field.
To tell Access that you want a Totals query, click the Design tab and then click the Totals icon in the
Show/Hide group. A new row called the Total row opens in the lower part of the QBE screen. At this point,
the screen resembles that in Figure B-21.

Search JabSto ::

Custom Search