orders have been placed this year or the total dollar value of all orders placed. The easiest way to get this information is to create a query that groups the necessary fields and does the math for you. To do this, you use aggregate functions in the query. Access queries support the following aggregate functions:
Function Calculates
Sum Total of the values in a field Avg Average of the values in a field
Count Number of values in a field, not counting Null (blank) values Min Lowest value in a field
Max Highest value in a field
StDev Standard deviation of the values in a field Var Variance of the values in a field
In this exercise, you will create a query that calculates the total number of products in The Garden Company’s inventory, the average price of all the products, and the total value of the inventory.
USE the GardenCo database in the practice file folder for this topic. This practice file is located in the
My Documents\Microsoft Press\Access 2003 SBS\Queries\Aggregate folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.
OPEN the GardenCo database and acknowledge the safety warning, if necessary.
1
On the Objects bar, click Queries, and then double-click Create query in Design view. Access first opens the query window in Design view and then displays the Show Table dialog box.2
In the Show Table dialog box, double-click Products, and click Close.Access adds the Products table to the query window and closes the Show Table dialog box.
3
In the list of fields in the Products table, double-click ProductID and then UnitPrice. Access moves both fields to the design grid.4
Click the Totals button on the toolbar.Totals A row named Total is added to the design grid.
5
Click in the Total cell of the ProductID column, click the down arrow, and click Count in the drop-down list.Access enters the word Count in the Total cell. When you run the query, this function will return a count of the number of records containing a value in the ProductID field.
6
In the UnitPrice column, set the Total cell to Avg.When you run the query, this function will return the average of all the UnitPrice values.
7
Click the Run button.8
Click the View button to return to Design view.View
9
In the Field cell of the third column, type UnitPrice*UnitsInStock and press F. The text you typed is changed to Expr1: [UnitPrice]*[UnitsInStock]. This expression will multiply the price of each product by the number of units in stock.10
Set the Total cell of the third column to Sum to return the sum of all the values calculated by the expression.11
Select Expr1:, and type Value of Inventory:.12
Run the query again.13
Close the query window, clicking No when prompted to save the query. CLOSE the GardenCo database.Key Points
■ Microsoft Office Access 2003 provides a variety of tools you can use to organize the display of information in a database and to locate specific items of infor mation, making it easy to search through and find information in your database, even as it grows in size and complexity.
■ You can sort through a table in either ascending or descending order, based on the values in any field (or combination of fields). In Access, numbers can be treated as text or numerals.
■ You can filter a table so that information containing some combination of characters is displayed (or excluded from the display). You can apply simple filters while viewing information in a table or a form. These filters are applied to the contents of a selected field, but you can apply another filter to the results of the first one to further refine your search.
■ You can use the Filter By Form command to filter a table or form based on the information in several fields. Since defining a filter is often easier in a form and viewing the results is easier in a table, you can use AutoForm to quickly create a form for a table. You can use the form with Filter By Form, and then switch to Datasheet view to see the results.
■ When you need to search a single table for records that meet multiple criteria or that require complex expressions as criteria, you can use the Advanced Filter/Sort command.
■ You can create queries to display specific fields from specific records from one or more tables, even designing the query to perform calculations for you. You can then save your queries for later use.
restrict data, page 146
Use Field Size properties to restrict data, page 150
Use validation rules to restrict data, page 157
Use lookup lists to restrict data, page 160
Update information Use input masks to