Databases Reference
In-Depth Information
When you create a query that involves a view, the DBMS changes the query to one that selects data from
the table(s) in the database that created the view. Suppose, for example, Juan creates the following query:
SELECT *
FROM Housewares
WHERE OnHand<25
;
120
The DBMS does
not
execute the query in this form. Instead, it merges the query Juan entered with the
query that defines the view to form the query that is actually executed. When the DBMS merges the query that
creates the view with the query to select rows where the OnHand value is less than 25, the query that the
DBMS actually executes is as follows:
SELECT PartNum, Description, OnHand, Price
FROM Part
WHERE Class='HW'
AND OnHand<25
;
In the query that the DBMS executes, the FROM clause lists the Part table rather than the Housewares
view, the SELECT clause lists fields from the Part table instead of * to select all fields from the Housewares view,
and the WHERE clause contains a compound condition to select only those parts in the HW class (as Juan sees
in the Housewares view) and only those parts with OnHand values of less than 25.
Juan, however, is unaware that this kind of activity is taking place. To Juan, it seems as though he is using
a table named Housewares. One advantage of this approach is that because the Housewares view never exists
in its own right, any update to the Part table is
immediately
available in Juan's Housewares view. If the
Housewares view were really a table, that would not be the case.
To create a view in Access, you simply create and save a query. For example, to create the Housewares
view, you would include the PartNum, Description, OnHand, and Price fields from the Part table. You would also
include the Class field in the design grid and enter HW as the criterion. Because the Class field isn't included
in the view, you would remove the check mark from the Class field's Show check box. Finally, you would
save the query using the name Housewares, as shown in Figure 4-3.
View name
Fields included
in the view
Class values won't
appear in the view
Condition to
select only those parts
in class HW
FIGURE 4-3
Access query design of the Housewares view
