Views - Database Design for Mere Mortals - page 387

If you recall, you created a calculated field list earlier in the design process (refer to
Chapter 6 , “ Analyzing theCurrent Database ” ).Youcannowusethislist asasourceofcal-
culated fields that you might (or should) use in your views. Review the list as you define
each new view and determine whether you can use one of the calculated fields on the list.
When you find one that you can use, create it in the same manner as you did in the pre-
ceding examples. (If you create a new calculated field that does not appear on your list,
however, be sure to add it to the list. This will help you keep your calculated field list cur-
rent and in order.)
Imposing Criteria to Filter the Data
Views have another characteristic that makes them extremely useful: You can impose cri-
teriaagainstoneormorefieldsintheviewtofiltertherecordsitdisplays.Forexample,say
that the CUSTOMER CALL LIST view included the C UST S TATE field. Although the view
would continue to display the set of records it did before, you would also see the state in
which each customer lives. Assume, however, that you want the view to show a particular
set of records, such as those for customers who live in Washington. You can accomplish
this by setting a specific criterion on the C UST S TATE field that will filter the data so that the
view displays only those records of customers from Washington.
Note
In database work, the word criterion refers to an expression that is tested against
the value of a particular field. The view will include a given record if the value of
the field meets the criterion.
This is the expression you will use to filter the records for the CUSTOMER CALL LIST
view:
CustState = “WA”
Now the view will display only customers from Washington. If you want to filter the re-
cords further to show only those customers who live in specific cities, you add a criterion
such as this:
CustCity In (“Bellevue,” “Olympia,” “Redmond,” “Seattle,”
“Spokane,” “Tacoma”)
TheviewwillnowdisplayWashingtoncustomers wholiveinthecities specified intheex-
pression. You may wonder why both criteria are necessary—the criterion for the C UST C ITY
field should retrieve the appropriate records by itself. The trouble is that many cities are
named for other cities, so cities in two or three different states could have the same name.
For example, there is a Portland, Oregon, and a Portland, Maine, both named after Port-