Views - Database Design for Mere Mortals - page 385

For example, consider the new CUSTOMER CALL LIST view. Although you have the
fields you need for the view, you'll have to make one minor modification to the view so
that it can display the appropriate data. Oneofthe requirements forthis view isthat it must
display the date of the last purchase made by each customer. In order to retrieve and dis-
play the proper date, you'll have to add a calculated field to the view. This field will use
the Maximum function [commonly known as Max( )] to retrieve the correct date from the
O RDER D ATE field.NamethenewfieldL AST P URCHASE D ATE andaddittotheCUSTOMER
CALL LIST view diagram. (You no longer need the O RDER D ATE field in the view, so you
can remove it from the view's structure.) This is the expression you'll use in the calculated
field to retrieve the appropriate date:
Max(Order Date)
Later in this section, you'll learn where and how to record this expression.
Note
Be sure to refer to your RDMBS's documentation to determine the correct syntax
for this function and all of the other functions used in this chapter.
Another calculated field you might include in this view is one that displays the complete
customernamebyconcatenatingC UST F IRST N AME andC UST L AST N AME .Say,forexample,
that you want to display the customer name in this manner: “Hernandez, Michael.” Create
a calculated field called C USTOMER N AME and use the following concatenation expression:
CustLast Name & “, “ & CustFirst Name
Add the new calculated field to the CUSTOMER CALL LIST view diagram and remove
theC UST F IRST N AME andC UST L AST N AME fieldsfromtheview;youdon'tneedthesefields
anymore because you're now using the C USTOMER N AME calculated field. (You'll soon
properly record this expression as well.)
Figure 12.15 shows how your revised view diagram should look after you've completed
these modifications.