Database Reference
In-Depth Information
Calculating by using queries
As you saw in the previous exercise, you can not only use a query to summarize data by
using built-in aggregate functions but you can also perform a calculation and create a new
field in which to store it. For example, you might want to calculate an extended price or
how long employees have worked for the company.
One of the basic tenets of good database design is that you should never store information
that can be calculated from existing data. Instead of creating a new field in a table and in-
creasing the size of the database with redundant information, use an expression in a query
to compute the desired information from existing data whenever you need it.
TIP
It is possible to use the results of one query as a field in another query. The nested
query involves use of a Structured Query Language (SQL) Select statement and is called
a
subquery
. For more information about subqueries, search for
Nest a query inside
another query
in Access Help.
In this exercise, you'll create a query that combines information from two tables into a
datasheet and calculates the extended price of an item based on the unit price, quantity
ordered, and discount.
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
Order Details
and
Products
tables to the
query.
2
Drag the following five fields from their field lists to consecutive columns in the
design grid.
From this field list
Drag this field
Order Details
OrderID
Products
ProductName
Order Details
UnitPrice
Order Details
Quantity
Order Details
Discount
