Creating a View

A view is a virtual table that is logically defined over one or more tables. Its definition includes a SELECT statement that determines the fields as well as the values it can contain at any point in time. Views permit you to query subsets of data or joins of multiple tables as though they are an actual table. Only the view definitions are stored - not the data retrieved by the view. Some views can be updated. An update of a view affects the underlying table. Views may make it easier to access data by organizing it in more meaningful ways, or by restricting the rows retrieved to those that pertain to the needs of a particular application. Views can also be used for security reasons, since privileges can be granted on a view instead of on the underlying table.

You can use a SELECT command generated through the Query Design window as the basis of a CREATE VIEW statement.

To create a view from a SELECT statement:

1. Create a new query or open a saved query. It is a good idea to run the query to see if it was correctly designed, and that it retrieves the rows you expected it would.
2. Switch to SQL view using the SQL button on the toolbar.
3. Position the cursor in front of the SELECT clause of your query.
4. To create a view called TodaysOrders, type the clause CREATE VIEW TodaysOrders AS in front of the SELECT statement.
   Note:

   You cannot create a view using a SELECT statement that contains an ORDER BY or UNION.

   The simplest way to determine whether or not a view is updatable is to try to create it with the WITH CHECK OPTION clause. If the view cannot be updated, an error will occur when you try to create it.