Alison Balter's Mastering Access 2002 Enterprise Development

Creating Complex Views

Views can contain complex joins and subqueries. The sections that follow cover these and other advanced topics.

Creating Views Based on Data in Multiple Tables

Creating a view based on data from multiple tables is quite simple. While in the View Builder, you use the Add Table tool on the toolbar to add additional tables to the view. SQL Server automatically joins the tables based on relationships established in the database.

Views and Subqueries

Just as a T-SQL statement can contain a subquery (see Chapter 6, “Mastering T-SQL”), a view can contain a subquery. The syntax looks like this:

 CREATE VIEW vwCustomersWithoutOrders AS SELECT CustomerID, CompanyName, ContactName FROM Customers WHERE NOT EXISTS (SELECT CustomerID ...