Database Reference
In-Depth Information
In this exercise, you'll create a query that calculates the total number of products in an
inventory, the average price of all the products, and the total value of the inventory.
SET UP
You need the GardenCompany07 database you worked with in the preceding
exercise to complete this exercise. If necessary, open the database. Then follow the steps.
1
Open the
Query Designer
, and add the
Products
table to the query.
2
In the
Products
field list, double-click
ProductID
, and then double-click
UnitPrice
.
3
On the
Design
tool tab, in the
Show/Hide
group, click the
Totals
button to add a
Total
row below the
Table
row in the design grid, with the
Group By
aggregate
function in the
Total
row of each field.
TIP
If you need to adjust the height of the design grid after adding the Total row,
drag the bar that separates the design grid and the top pane upward.
4
Click in the
Total
row of the
ProductID
field, click the arrow, and then in the list,
click
Count
to replace
Group By
with the
Count
aggregate function.
5
Display the
Total
list for the
UnitPrice
field, and click
Avg
.
This simple query summarizes the data in two fields in different ways.
Let's run the query to count the number of records containing a value in the
ProductID field and the average of all the UnitPrice values.
6
Run the query.
The results show that the average price of 189 products is $17.92.
